<?php

namespace app\service;

use app\exception\HttpResponseException;
use app\model\DelayOrderModel;
use app\model\EntityModel;
use app\model\EpisodeStepCategorySealTimeModel;
use app\model\OrderModel;
use app\model\PlanModel;
use app\model\ReviewRequestModel;
use app\model\StatusModel;
use app\model\SupplierModel;
use app\model\TaskModel;
use app\model\TaskRelationTagModel;
use app\model\TaskWorkOrder;
use app\model\TaskWorkOrderModel;
use app\model\TenantUserModel;
use app\tool\SingletonTrait;
use support\Request;
use teamones\filter\Enforcer;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use think\db\Query;
use think\facade\Db;
use think\model\Collection;

class WorkbenchService
{

    use SingletonTrait;

    /**
     * 各环节对应类型
     * @var array[]
     */
    public $prepareNameList;

    public function __construct()
    {
        $this->prepareNameList = [
            '关卡' => ['关卡'],
            '绑定' => ['角色', '道具'],
            '模型' => ['角色', '道具', '场景'],
            '美术' => ['角色', '道具', '关卡', '场景'],
        ];
    }

    /**
     * 组内完成情况
     * @param Request $request
     * @param int $projectId
     * @param int $startTime
     * @param int $endTime
     * @param array $todoStepCategoryIdList
     * @param int $departmentId
     * @param int $today
     * @return int[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function groupPlanDoneCount(Request $request, int $projectId, int $startTime, int $endTime, array $todoStepCategoryIdList, int $departmentId, int $today): array
    {
        $res = [
            'task_actually_done' => 0,
            'task_actually_done_standard_todo' => 0,
            'task_actually_done_step' => 0,
            'task_plan_done' => 0,
            'task_plan_done_standard_todo' => 0,
            'task_plan_done_step' => 0,
            'task_plan_total' => 0,
            'task_plan_total_standard_todo' => 0,
            'task_plan_total_step' => 0,
            'end_time' => 0
        ];
        $userIdList = [];
        if ($departmentId > 0) {
            $userIdList = TenantUserModel::join("user", "user.union_id = tenant_user.user_id")
                ->join('tenant_user_department', 'tenant_user_department.tenant_user_id = tenant_user.id')
                ->where("tenant_user_department.department_id", $departmentId)
                ->column('distinct user.id');
            if (empty($userIdList)) {
                return $res;
            }
        }

        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];

        $qresTotal = $this->getGroupPlanDoneCountBaseQuery($request, $projectId, $startTime, $endTime, $todoStepCategoryIdList, $userIdList)
            ->whereIn("task.step_type", [
                TaskModel::STEP_TYPE_STEP,
                TaskModel::STEP_TYPE_STANDARD_MAKE_TO_DO,
                TaskModel::STEP_TYPE_TODO,
            ])
            ->select();

        foreach ($qresTotal as $qresTotalItem) {
            $res['task_plan_total'] += $qresTotalItem['count'];
            $res['task_plan_total_step'] += $qresTotalItem['count'];
        }

        $doneStatusId = StatusModel::whereIn("correspond", ["publish", "done"])->column('id');
        $qresDone = $this->getGroupPlanDoneCountBaseQuery($request, $projectId, $startTime, $endTime, $todoStepCategoryIdList, $userIdList)
            ->whereIn("task.step_type", [TaskModel::STEP_TYPE_STEP, TaskModel::STEP_TYPE_STANDARD_MAKE_TO_DO])
            ->whereFieldRaw("get_json_string(task.`json`,'$.task_status')", "IN", $doneStatusId)
            ->select();

        foreach ($qresDone as $qresDoneItem) {
            $res['task_plan_done'] += $qresDoneItem['count'];
            $res['task_plan_done_step'] += $qresDoneItem['count'];
        }


        $qresActuallyDone = $this->getGroupPlanActuallyDoneCount($request, $projectId, $startTime, $endTime, $todoStepCategoryIdList, $userIdList, ["step", "standard_make_to_do"], []);

        foreach ($qresActuallyDone as $qresActuallyDoneItem) {
            $res['task_actually_done'] += $qresActuallyDoneItem['count'];
            $res['task_actually_done_step'] += $qresActuallyDoneItem['count'];
        }

        if (!empty($todoStepCategoryIdList)) {
            $userIdList = TenantUserModel::join("user", "user.union_id = tenant_user.user_id")
                ->join('tenant_user_department', 'tenant_user_department.tenant_user_id = tenant_user.id')
                ->join("department", "department.id = tenant_user_department.department_id")
                ->whereFieldRaw("array_length(array_intersect(split(department.step_category_ids,','),[" . implode(',', $todoStepCategoryIdList) . "]))", '>', 0)
                ->where("tenant_user.tenant_id", $request->getTenantId())
                ->field('distinct user.id')
                ->column('user.id');
        }

        if (!empty($userIdList)) {
            $qresOtherStandardDone = $this->getGroupPlanDoneCountBaseQuery($request, $projectId, $startTime, $endTime, $todoStepCategoryIdList, $userIdList)
                ->whereNotIn("task.step_type", [TaskModel::STEP_TYPE_STEP, TaskModel::STEP_TYPE_STANDARD_MAKE_TO_DO])
                ->whereFieldRaw("get_json_string(task.`json`,'$.task_status')", $finalStatusId)
                ->select();

            foreach ($qresOtherStandardDone as $qresOtherStandardDoneItem) {
                $res['task_plan_done'] += $qresOtherStandardDoneItem['count'];
                if (in_array($qresOtherStandardDoneItem['step_type'], [TaskModel::STEP_TYPE_STANDARD_MEETING, TaskModel::STEP_TYPE_STANDARD_REVIEW])) {
                    $res['task_plan_done_standard_todo'] += $qresOtherStandardDoneItem['count'];
                }
//                else {
                //res.TaskPlanDoneOther += item.Count
//                }
            }

            $qresOtherStandardTotal = $this->getGroupPlanDoneCountBaseQuery($request, $projectId, $startTime, $endTime, $todoStepCategoryIdList, $userIdList)
                ->whereNotIn("task.step_type", [TaskModel::STEP_TYPE_STEP, TaskModel::STEP_TYPE_STANDARD_MAKE_TO_DO])
                ->select();
            foreach ($qresOtherStandardTotal as $qresOtherStandardTotalItem) {
                $res['task_plan_total'] += $qresOtherStandardTotalItem['count'];

                if (in_array($qresOtherStandardTotalItem['step_type'], [TaskModel::STEP_TYPE_STANDARD_MEETING, TaskModel::STEP_TYPE_STANDARD_REVIEW])) {
                    $res['task_plan_total_standard_todo'] += $qresOtherStandardTotalItem['count'];
                }
//                else {
                //res.TaskPlanTotalOther += item.Count
//                }
            }
            $qresActuallyDone = $this->getGroupPlanActuallyDoneCount($request, $projectId, $startTime, $endTime, $todoStepCategoryIdList, $userIdList, [], ["step", "standard_make_to_do"]);
            foreach ($qresActuallyDone as $qresActuallyDoneItem) {
                $res['task_actually_done'] += $qresActuallyDoneItem['count'];
                if (in_array($qresActuallyDoneItem['step_type'], [TaskModel::STEP_TYPE_STANDARD_MEETING, TaskModel::STEP_TYPE_STANDARD_REVIEW])) {
                    $res['task_actually_done_standard_todo'] += $qresActuallyDoneItem['count'];
                }
//                else {
                //res.TaskActuallyDoneOther += item.Count
//                }
            }
        }
        $res['end_time'] = date('Y-m-d', $endTime);
        return [$res];
    }

    /**
     * 获得计划完成查询对象
     * @param Request $request
     * @param int $projectId
     * @param int $startTime
     * @param int $endTime
     * @param array $todoStepCategoryIdList
     * @param array $userIdList
     * @return TaskModel|Query
     */
    private function getGroupPlanDoneCountBaseQuery(Request $request, int $projectId, int $startTime, int $endTime, array $todoStepCategoryIdList, array $userIdList)
    {
        $q = TaskModel::field("count(distinct task.id) count,task.step_type step_type")
            ->where("task.tenant_id", $request->getTenantId())
            ->whereRaw("task.step_type <>'step_category' and get_json_string(task.`json`,'$.task_status') <> 3")
            ->whereBetween("task.plan_end_time", [$startTime, $endTime]);
        if ($projectId > 0) {
            $q->where("task.project_id", $projectId);
        }
        if (!empty($userIdList)) {
            $q->join("task_member", "task_member.task_id = task.id")
                ->whereIn("task_member.partner", $userIdList);
        } else if (empty($todoStepCategoryIdList)) {
            $q->join("task_member", "task_member.task_id = task.id")
                ->whereIn("task_member.partner", $request->getSaasUserId());
        }
        if (!empty($todoStepCategoryIdList)) {
            $q->join("task_relation_tag r_step_category", "r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')")
                ->whereIn("r_step_category.link_id", $todoStepCategoryIdList);
        }
        return $q->group("task.step_type");
    }

    /**
     * 计划交付统计
     * @param Request $request
     * @param int $projectId
     * @param int $startTime
     * @param int $endTime
     * @param array $todoStepCategoryIdList
     * @param array $userIdList
     * @param array $stepType
     * @param array $notInStepType
     * @return TaskModel|array|\think\Collection|Query|Collection
     */
    private function getGroupPlanActuallyDoneCount(Request $request, int $projectId, int $startTime, int $endTime, array $todoStepCategoryIdList, array $userIdList, array $stepType, array $notInStepType)
    {
        $doneStatusId = StatusModel::whereIn("correspond", ["publish", "done"])->cache()->column('id');
        $q = TaskModel::field("count(distinct task.id) count,task.step_type step_type")
            ->where("task.tenant_id", $request->getTenantId())
            ->where("task.step_type", '<>', 'step_category')
            ->whereFieldRaw("get_json_string(task.`json`,'$.task_status') ", '<>', 3)
            ->whereBetween('task.end_time', [$startTime, $endTime])
            ->whereFieldRaw("get_json_string(task.`json`,'$.task_status')", "in", $doneStatusId);
        if (!empty($projectId > 0)) {
            $q->where("task.project_id", $projectId);
        }
        if (!empty($userIdList)) {
            $q->join('task_member', 'task_member.task_id = task.id')->whereIn("task_member.partner", $userIdList);
        } else {
            $q->join('task_member', 'task_member.task_id = task.id')->whereIn("task_member.partner", $request->getSaasUserId());
        }
        if (!empty($todoStepCategoryIdList)) {
            $q->join("task_relation_tag r_step_category", "r_step_category.task_id = task.id and r_step_category.link_module_code in ('step_category','show_step_category')")
                ->whereIn("r_step_category.link_id", $todoStepCategoryIdList);
        }
        if (!empty($stepType)) {
            $q->whereIn("task.step_type", $stepType);
        }

        if (!empty($notInStepType)) {
            $q->whereRaw("!array_contains(['" . implode("','", $notInStepType) . "'], task.step_type)");
        }
        return $q->group("task.step_type")->select();

    }

    /**
     * 剧集详情
     * @param $projectId
     * @param $startTime
     * @param $endTime
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function episodeDetail($projectId, $startTime, $endTime): array
    {
        $field = [
            'id as episode_id',
            'name as episode_name',
            "get_json_string(`json`,'$.episode_custom_grade') as episode_custom_grade",
            "get_json_string(`json`,'$.episode_plan_seal_time') as episode_plan_seal_time",
            "get_json_string(`json`,'$.episode_seal_time') as episode_seal_time"
        ];

        $db = (new EntityModel)->field($field);
        if (!empty($projectId)) {
            $db->where('project_id', $projectId);
        }
        if (!empty($startTime) && !empty($endTime)) {
            $db->whereFieldRaw("get_json_string(`json`,'$.episode_plan_seal_time')", 'between', [$startTime, $endTime]);
        }
        $list = $db->order('episode_plan_seal_time,name')->select();
        if (empty($list->toArray())) {
            return [];
        }
        return array_map(function ($tmp) {
            $tmp['episode_id'] = intval($tmp['episode_id']);
            $tmp['episode_plan_seal_time'] = intval($tmp['episode_plan_seal_time']);
            $tmp['episode_seal_time'] = intval($tmp['episode_seal_time']);
            $tmp['episode_custom_grade'] = (string)$tmp['episode_custom_grade'];
            return $tmp;
        }, $list->toArray());
    }

    /**
     * 环节封板剧集情况查询
     * @param $param
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function stepCategorySealEpisodeList($param): array
    {
        $db = (new EpisodeStepCategorySealTimeModel)->field('entity.id as episode_id,entity.code as episode_code');
        $db->join('entity', 'entity.id = episode_step_category_seal_time.entity_id');
        if (!empty($param['start_time'])) {
            $db->whereFieldRaw("episode_step_category_seal_time.seal_time", 'between', [$param['start_time'], time()]);
        }
        if (!empty($param['project_id'])) {
            $db->where('episode_step_category_seal_time.project_id', $param['project_id']);
        }
        if (!empty($param['step_category_id_list'])) {
            $stepCategoryIdListWhere = '';
            foreach ((array)$param['step_category_id_list'] as $stepCategoryIdItem) {
                $stepCategoryIdListWhere .= " or (find_in_set({$stepCategoryIdItem}, episode_step_category_seal_time.step_category_ids) > 0)";
            }
            $stepCategoryIdListWhere = trim($stepCategoryIdListWhere, ' or');
            $db->whereRaw($stepCategoryIdListWhere);
        }
        $sealEpisodeData = $db->select();
        if (empty($sealEpisodeData->toArray())) {
            return [];
        }
        $sealEpisodeData = $sealEpisodeData->toArray();
        $res = $this->queryStepCategoryEpisodeDetail($param, $param['current_show_name'], $sealEpisodeData, false);
        if (empty($res)) {
            return [];
        }
        return $res;
    }

    /**
     * 查询环节剧集详情
     * @param $param
     * @param $currentShowName
     * @param $sealEpisodeData
     * @param $needTimeFilter
     * @return array|mixed|void
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function queryStepCategoryEpisodeDetail($param, $currentShowName, $sealEpisodeData, $needTimeFilter)
    {
        $reviewOptions = OptionsService::getInstance()->getProjectReviewOptions($param['project_id']);
        $reviewOptionMap = [];
        if (!empty($reviewOptions)) {
            $reviewOptionMap = array_column($reviewOptions, null, 'id');
        }
        // 查询排期要完成的
        $episodeIdList = [];
        foreach ($sealEpisodeData as $sealEpisodeKey => $sealEpisodeItem) {
            $episodeIdList[$sealEpisodeKey] = $sealEpisodeItem['episode_id'];
            if (!empty($sealEpisodeItem['review_options_id'])) {
                if (!empty($reviewOptionMap[$sealEpisodeItem['review_options_id']])) {
                    $sealEpisodeData[$sealEpisodeKey]['review_meeting_name'] = $reviewOptionMap[$sealEpisodeItem['review_options_id']];
                }
            }
        }
        $sealEpisodeData = $this->getShowStepCategoryCountItem($currentShowName, $sealEpisodeData, $needTimeFilter, $param['project_id'], $param['step_category_id_list'], $episodeIdList, $param['start_time'] ?? 0, $param['end_time'] ?? 0);
        // 查询剧集基本信息
        $episodeList = (new EntityModel)->field("id,name,get_json_string(`json`,'episode_custom_grade') episode_custom_grade")
            ->whereIn("id", $episodeIdList)
            ->select();
        if (empty($episodeList->toArray())) {
            return $sealEpisodeData;
        }
        $episodeMap = array_column($episodeList->toArray(), null, 'id');
        foreach ($sealEpisodeData as $i => $re) {
            if (!empty($episodeMap[$re['episode_id']])) {
                $sealEpisodeData[$i]['episode_name'] = $episodeMap[$re['episode_id']]['name'] ?? '';
                $sealEpisodeData[$i]['episode_custom_grade'] = $episodeMap[$re['episode_id']]['episode_custom_grade'] ?? '';
            }
        }
        return $sealEpisodeData;
    }

    /**
     * 获取环节统计详情
     * @param $currentShowName
     * @param $sealEpisodeData
     * @param $needTimeFilter
     * @param $projectId
     * @param $stepCategoryIdList
     * @param $episodeIdList
     * @param $startTime
     * @param $endTime
     * @return array|mixed
     */
    public function getShowStepCategoryCountItem($currentShowName, $sealEpisodeData, $needTimeFilter, $projectId, $stepCategoryIdList, $episodeIdList, $startTime, $endTime)
    {
        if (!empty($currentShowName) && in_array($currentShowName, ['美术', '模型', '绑定', '关卡'])) {
            // 查询剧集任务实体数量
            $episodeEntityCountList = $this->getEpisodeEntityTaskCountList($needTimeFilter, $startTime, $endTime, $projectId, $stepCategoryIdList);
            if (empty($episodeEntityCountList)) {
                return $sealEpisodeData;
            }
            $episodeEntityCountListMap = [];
            foreach ($episodeEntityCountList as $episodeEntityCountDetail) {
                $episodeEntityCountDetailTmpKey = $episodeEntityCountDetail['episode_id'] . '-' . $episodeEntityCountDetail['category_name'];
                $episodeEntityCountListMap[$episodeEntityCountDetailTmpKey] = $episodeEntityCountDetail;
            }
            // 查询剧集任务实体数量
            $episodeTaskDetailCountList = $this->getStepCategoryIdEntityTaskList($projectId, $stepCategoryIdList, $episodeIdList);
            if (empty($episodeTaskDetailCountList)) {
                return $sealEpisodeData;
            }
            $episodeTaskDetailCountListMap = [];
            foreach ($episodeTaskDetailCountList as $episodeTaskDetailCountDetail) {
                $episodeIdCategoryNameTmpKey = $episodeTaskDetailCountDetail['episode_id'] . '-' . $episodeTaskDetailCountDetail['category_name'];
                $episodeTaskDetailCountListMap[$episodeIdCategoryNameTmpKey] = $episodeTaskDetailCountDetail;
            }
            $tmpTaskDone = 0;
            $tmpTaskTotal = 0;
            foreach ($sealEpisodeData as $i => $re) {
                foreach ($this->prepareNameList[$currentShowName] as $categoryNameIndex => $categoryName) {
                    $sealEpisodeData[$i]['asset_task_count_detail_list'][] = [
                        'episode_id' => intval($re['episode_id']),
                        'category_name' => $categoryName,
                        'total' => 0,
                        'done' => 0,
                        'entity_number' => 0,
                    ];
                    $k = $re['episode_id'] . '-' . $categoryName;
                    if (!empty($episodeEntityCountListMap[$k])) {
                        $sealEpisodeData[$i]['asset_task_count_detail_list'][$categoryNameIndex]['entity_number'] = intval($episodeEntityCountListMap[$k]['entity_number']);
                    }
                    if (!empty($episodeTaskDetailCountListMap[$k])) {
                        $tmpTaskDone += $episodeTaskDetailCountListMap[$k]['total'] ?? 0;
                        $tmpTaskTotal += $episodeTaskDetailCountListMap[$k]['done'] ?? 0;
                        $sealEpisodeData[$i]['asset_task_count_detail_list'][$categoryNameIndex]['total'] = intval($episodeTaskDetailCountListMap[$k]['total']);
                        $sealEpisodeData[$i]['asset_task_count_detail_list'][$categoryNameIndex]['done'] = intval($episodeTaskDetailCountListMap[$k]['done']);
                        $sealEpisodeData[$i]['task_total'] = intval($tmpTaskDone);
                        $sealEpisodeData[$i]['task_done'] = intval($tmpTaskTotal);
                    }
                }
            }
        }
        if (!empty($currentShowName) && in_array($currentShowName, ['动画', '解算', '灯光', '特效', '合成'])) {
            $episodeTaskCountList = $this->getEpisodeTaskCountList($projectId, $stepCategoryIdList);
            if (empty($episodeTaskCountList)) {
                return $sealEpisodeData;
            }
            $episodeTaskCountMap = array_column($episodeTaskCountList, null, 'episode_id');
            // 查询剧集任务实体数量
            $episodeEntityCountList = $this->getEpisodeEntityCountList($projectId, $needTimeFilter, $stepCategoryIdList, $startTime, $endTime);
            if (empty($episodeEntityCountList)) {
                return $sealEpisodeData;
            }
            $episodeEntityCountMap = array_column($episodeEntityCountList, null, 'episode_id');
            foreach ($sealEpisodeData as $i => $re) {
                if (!empty($episodeEntityCountMap[$re['episode_id']])) {
                    $sealEpisodeData[$i]['entity_total'] = $episodeEntityCountMap[$re['episode_id']]['entity_number'] ?? 0;
                }
                if (!empty($episodeTaskCountMap[$re['episode_id']])) {
                    $sealEpisodeData[$i]['task_total'] = $episodeTaskCountMap[$re['episode_id']]['total'] ?? 0;
                    $sealEpisodeData[$i]['task_done'] = $episodeTaskCountMap[$re['episode_id']]['done'] ?? 0;
                }
            }
        }
        return $sealEpisodeData;
    }

    /**
     * 查询剧集任务实体数量
     * @param $projectId
     * @param $needTimeFilter
     * @param $stepCategoryIdList
     * @param $startTime
     * @param $endTime
     * @return array
     */
    public function getEpisodeEntityCountList($projectId, $needTimeFilter, $stepCategoryIdList, $startTime, $endTime): array
    {
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $field = [
            'task.episode_id episode_id',
            'count(distinct task.entity_id) entity_number',
        ];
        $etqDb = TaskModel::field($field);
        // if (!empty($episodeIdList)) {
        //     $etqDb->where('task.step_type', TaskModel::STEP_TYPE_STEP);
        //     $etqDb->whereIn('task.episode_id', $episodeIdList);
        // }
        if (!empty($projectId)) {
            $etqDb->where("task.project_id", $projectId);
        }
        if (!empty($stepCategoryIdList)) {
            $etqDb->whereIn('task.step_category_id', $stepCategoryIdList);
        }
        if ($needTimeFilter) {
            $etqDb->whereFieldRaw('task.plan_end_time', 'between', [$startTime, $endTime]);
        }
        $episodeEntityCountList = $etqDb
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedId)
            ->group("task.episode_id")
            ->select();
        if (empty($episodeEntityCountList->toArray())) {
            return [];
        }
        return $episodeEntityCountList->toArray();
    }

    /**
     * 获取集任务数量
     * @param $projectId
     * @param $stepCategoryIdList
     * @return array
     */
    public function getEpisodeTaskCountList($projectId, $stepCategoryIdList): array
    {
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $finalId = StatusService::getInstance()->byCode('final')['id'];
        $readyToPublishId = StatusService::getInstance()->byCode('ready_to_publish')['id'];
        $field = [
            'task.episode_id as episode_id',
            'count(task.id) as total',
            "count(get_json_string(task.`json`,'task_status') in ($finalId,$readyToPublishId) or null) done",
        ];
        $etqDb = (new TaskModel)->field($field);
        // if (!empty($episodeIdList)) {
        //     $etqDb->where('task.step_type', TaskModel::STEP_TYPE_STEP);
        //     $etqDb->whereIn(' task.episode_id', $episodeIdList);
        // }
        if (!empty($projectId)) {
            $etqDb->where("task.project_id", $projectId);
        }
        if (!empty($stepCategoryIdList)) {
            $etqDb->whereIn('task.step_category_id', $stepCategoryIdList);
        }
        $episodeTaskCountList = $etqDb
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedId)
            ->group('task.episode_id')->select();
        if (empty($episodeTaskCountList->toArray())) {
            return [];
        }
        return $episodeTaskCountList->toArray();
    }

    /**
     * 查询剧集环节实体数量
     * @param $projectId
     * @param $stepCategoryIdList
     * @param $episodeIdList
     * @return array
     */
    public function getStepCategoryIdEntityTaskList($projectId, $stepCategoryIdList, $episodeIdList): array
    {
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $finalId = StatusService::getInstance()->byCode('final')['id'];
        $readyToPublishId = StatusService::getInstance()->byCode('ready_to_publish')['id'];
        $field = [
            'task.episode_id as  episode_id',
            "if(module.code = 'design',get_json_string(entity.`json`, 'design_category'),if(module.code = 'asset', get_json_string(entity.`json`, 'asset_category'),get_json_string(entity.`json`, 'level_category'))) category_name",
            "count(task.id) total",
            "count(get_json_string(task.`json`,'task_status') in ($finalId,$readyToPublishId) or null) done",
        ];
        $etqDb = (new TaskModel)->field($field)
            ->join('entity', 'entity.id = task.entity_id')
            ->join('module', 'module.id = entity.module_id')
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedId);
        if (!empty($projectId)) {
            $etqDb->where('task.project_id', $projectId);
        }
        if (!empty($stepCategoryIdList)) {
            $etqDb->whereIn('task.step_category_id', $stepCategoryIdList);
        }
        if (!empty($episodeIdList)) {
            $etqDb->whereIn('task.episode_id', $episodeIdList);
        }
        $episodeTaskDetailCountList = $etqDb->group('task.episode_id,category_name')->select();
        if (empty($episodeTaskDetailCountList->toArray())) {
            return [];
        }
        return $episodeTaskDetailCountList->toArray();
    }

    /**
     * 查询剧集任务实体数量
     * @param $needTimeFilter
     * @param $startTime
     * @param $endTime
     * @param $projectId
     * @param $stepCategoryIdList
     * @return array
     */
    public function getEpisodeEntityTaskCountList($needTimeFilter, $startTime, $endTime, $projectId, $stepCategoryIdList): array
    {
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $field = [
            'task.episode_id episode_id',
            "if(module.code = 'design', get_json_string(entity.`json`, 'design_category'),if(module.code = 'asset', get_json_string(entity.`json`, 'asset_category'),get_json_string(entity.`json`, 'level_category'))) category_name",
            'count(distinct task.entity_id) entity_number'
        ];
        $etqDb = TaskModel::field($field)
            ->join('entity', 'entity.id = task.entity_id')
            ->join('module', 'module.id = entity.module_id')
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedId);
        if ($needTimeFilter) {
            $etqDb->whereFieldRaw('task.plan_end_time', 'between', [$startTime, $endTime]);
        }
        if (!empty($projectId)) {
            $etqDb->where('task.project_id', $projectId);
        }
        if (!empty($stepCategoryIdList)) {
            $etqDb->whereIn('task.step_category_id', $stepCategoryIdList);
        }
        $episodeEntityCountList = $etqDb->group("task.episode_id,category_name")->select();
        if (empty($episodeEntityCountList->toArray())) {
            return [];
        }
        return $episodeEntityCountList->toArray();
    }

    /**
     * 环节进行中剧集列表
     * @param $param
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function stepCategoryInProgressEpisodeList($param): array
    {
        // 查询这些剧集里今天有审核排期的
        $reviewEpisodeIdList = $this->getPlanReviewEpisodeList($param['project_id'], $param['step_category_id_list']);

        // 查询本月封板了的环节剧集
        $sealEpisodeIdList = $this->getEpisodeStepCategorySealTime($param['project_id'], $param['step_category_id_list'], 1, time());

        // 查询排期要完成的剧集
        $pq = (new PlanModel)->field("entity.id as episode_id,entity.code as episode_code,min(plan.start_time) min_plan_start_time,max(plan.end_time) max_plan_end_time")
            ->join('entity', 'entity.id = plan.entity_id')
            ->join('task_relation_tag r_step_category', "link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id");

        if (!empty($param['project_id'])) {
            $pq->where('plan.project_id', $param['project_id']);
        }
        if (!empty($reviewEpisodeIdList)) {
            $reviewEpisodeIdList = join(',', $reviewEpisodeIdList);
            $pq->whereRaw("plan.entity_id NOT IN ({$reviewEpisodeIdList})");
        }
        if (!empty($sealEpisodeIdList)) {
            $sealEpisodeIdList = join(',', $sealEpisodeIdList);
            $pq->whereRaw("plan.entity_id NOT IN ({$sealEpisodeIdList})");
        }
        if (!empty($param['start_time']) && !empty($param['end_time'])) {
            $pq->whereFieldRaw('plan.start_time', '<=', $param['end_time'])
                ->whereFieldRaw('plan.end_time', '>=', $param['start_time']);
        }
        if (!empty($param['step_category_id_list'])) {
            $pq->whereIn('r_step_category.link_id', $param['step_category_id_list']);
        }

        $stepCategoryInProgressEpisodeList = $pq->where('plan.scope', 'single')->where('plan.type', 'step_category')
            ->order('entity.code ASC')->group('entity.id,entity.code')->select();
        if (empty($stepCategoryInProgressEpisodeList->toArray())) {
            return [];
        }
        $res = $this->queryStepCategoryEpisodeDetail($param, $param['current_show_name'], $stepCategoryInProgressEpisodeList->toArray(), true);
        if (empty($res)) {
            return [];
        }
        return $res;
    }

    /**
     * 查询这些剧集里今天有审核排期的
     * @param int $projectId
     * @param array $stepCategoryIdList
     * @return array
     */
    public function getPlanReviewEpisodeList(int $projectId, array $stepCategoryIdList): array
    {
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $finalId = StatusService::getInstance()->byCode('final')['id'];
        $previewQ = (new PlanModel)->field('distinct r_episode.link_id episode_id')
            ->join('task', 'task.id = plan.task_id')
            ->join('task_relation_tag r_step_category', "r_step_category.link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id")
            ->join('task_relation_tag r_episode', "r_episode.link_module_code in ('episode') and r_episode.task_id = plan.task_id");

        if (!empty($projectId)) {
            $previewQ->where('plan.project_id', $projectId);
        }
        if (!empty($stepCategoryIdList)) {
            $previewQ->whereIn('r_step_category.link_id', $stepCategoryIdList);
        }
        $reviewEpisodeIdListQ = $previewQ
            ->where('plan.type', 'review')
            ->whereFieldRaw('plan.end_time', '<=', strtotime(date('Y-m-d 23:59:59'), time()))
            ->whereRaw("get_json_string(task.`json`,'task_status') NOT IN ($closedId,$finalId)")
            ->select();
        if (empty($reviewEpisodeIdListQ->toArray())) {
            return [];
        }
        return array_column($reviewEpisodeIdListQ->toArray(), 'episode_id');
    }

    /**
     * 查询板了的环节剧集
     * @param int $projectId
     * @param array $stepCategoryIdList
     * @param $startTime
     * @param $endTime
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getEpisodeStepCategorySealTime(int $projectId, array $stepCategoryIdList, $startTime, $endTime): array
    {
        $episodeStepCategorySealTimeModel = (new EpisodeStepCategorySealTimeModel)->field("entity_id as episode_id")
            ->whereBetweenTime('episode_step_category_seal_time.seal_time', $startTime, $endTime);
        if (!empty($projectId)) {
            $episodeStepCategorySealTimeModel->where('episode_step_category_seal_time.project_id', $projectId);
        }
        if (!empty($stepCategoryIdList)) {
            $stepCategoryIdListWhere = '';
            foreach ($stepCategoryIdList as $stepCategoryIdItem) {
                $stepCategoryIdListWhere .= " or (find_in_set({$stepCategoryIdItem}, step_category_ids) > 0)";
            }
            $stepCategoryIdListWhere = trim($stepCategoryIdListWhere, ' or');
            $episodeStepCategorySealTimeModel->whereRaw($stepCategoryIdListWhere);
        }
        $sealEpisodeIdListQ = $episodeStepCategorySealTimeModel->select();

        if (empty($sealEpisodeIdListQ->toArray())) {
            return [];
        }
        return array_column($sealEpisodeIdListQ->toArray(), 'episode_id');
    }

    /**
     * 制作情况概览
     * @param $param
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function makingOverview($param): array
    {
        $assetMakeCategoryNumberRes = ['make_entity_total' => 0, 'review_detail_list' => null, 'make_episode_number' => 0, 'asset_make_category_number_list' => null];
        // 查询排期要完成的剧集
        $qpDb = (new PlanModel)->field('entity.id')
            ->join('entity', 'entity.id = plan.entity_id')
            ->join('task_relation_tag r_step_category', "link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id")
            ->where('plan.scope', 'single')
            ->where('plan.type', 'step_category');
        if (!empty($param['project_id'])) {
            $qpDb->where('plan.project_id', $param['project_id']);
        }
        if (!empty($param['start_time']) && !empty($param['end_time'])) {
            $qpDb->whereFieldRaw('plan.start_time', '<=', $param['end_time'])
                ->whereFieldRaw('plan.end_time', '>=', $param['start_time']);
        }
        if (!empty($param['step_category_id_list'])) {
            $qpDb->whereIn('r_step_category.link_id', $param['step_category_id_list']);
        }
        $planEpisodeIdList = $qpDb->column('entity.id');
        if (empty($planEpisodeIdList)) {
            return $assetMakeCategoryNumberRes;
        }
        $currentShowName = $param['current_show_name'] ?? '';
        switch ($currentShowName) {
            case '美术':
            case '模型':
            case '绑定':
            case '关卡':
                $episodeTaskDetailCountList = $this->getMakingOverviewAssetCountList($planEpisodeIdList, $param['project_id'], $param['step_category_id_list'], $param['start_time'], $param['end_time']);
                if (empty($episodeTaskDetailCountList)) {
                    return $assetMakeCategoryNumberRes;
                }
                $assetMakeCategoryNumberList = [];
                $episodeTaskDetailCountMap = array_column($episodeTaskDetailCountList, null, 'category_name');
                foreach ($this->prepareNameList[$currentShowName] as $name) {
                    $assetMakeCategoryNumberList[] = [
                        'name' => $name,
                        'number' => intval($episodeTaskDetailCountMap[$name]['number'] ?? 0),
                    ];
                }
                $assetMakeCategoryNumberRes['asset_make_category_number_list'] = $assetMakeCategoryNumberList;
                return $assetMakeCategoryNumberRes;
            case '动画':
            case '解算':
            case '灯光':
            case '特效':
            case '合成':
                // 查询本月制作总镜头数
                $makeEntityTotal = $this->getMakeShotTotal($param['start_time'], $param['end_time'], $param['step_category_id_list'], $planEpisodeIdList);
                if (empty($makeEntityTotal)) {
                    return $assetMakeCategoryNumberRes;
                }
                $assetMakeCategoryNumberRes['make_entity_total'] = intval($makeEntityTotal);
                // 查询审核记录
                $reviewDetailList = ReviewService::getInstance()->getReviewDetailList($planEpisodeIdList, null, $param['end_time'], $param['project_id'], $param['step_category_id_list']);
                if (!empty($reviewDetailList)) {
                    $assetMakeCategoryNumberRes['review_detail_list'] = $reviewDetailList;
                }
                return $assetMakeCategoryNumberRes;
            default:
                $assetMakeCategoryNumberRes['make_episode_number'] = count($planEpisodeIdList);
                return $assetMakeCategoryNumberRes;
        }
    }


    /**
     *
     * @param $planEpisodeIdList
     * @param $projectId
     * @param $stepCategoryIdList
     * @param $startTime
     * @param $endTime
     * @return array
     */
    public function getMakingOverviewAssetCountList($planEpisodeIdList, $projectId, $stepCategoryIdList, $startTime, $endTime): array
    {
        $field = [
            "if(module.code = 'design', get_json_string(entity.`json`, 'design_category'),if(module.code = 'asset', get_json_string(entity.`json`, 'asset_category'),get_json_string(entity.`json`, 'level_category'))) category_name",
            "count(distinct task.entity_id) number",
        ];
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $etqDb = (new TaskModel)->field($field)
            ->join('entity', 'entity.id = task.entity_id join module on module.id = entity.module_id')
            ->where('task.step_type', 'step')
            ->whereIn('task.episode_id', $planEpisodeIdList)
            ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '<>', $closedId)
            ->whereBetweenTime('task.plan_end_time', $startTime, $endTime);
        if (!empty($projectId)) {
            $etqDb->where('task.project_id', $projectId);
        }
        if (!empty($stepCategoryIdList)) {
            $etqDb->whereIn('task.step_category_id', $stepCategoryIdList);
        }
        $episodeTaskDetailCountList = $etqDb->group('category_name')->order('category_name')->select();
        if (empty($episodeTaskDetailCountList->toArray())) {
            return [];
        }
        return $episodeTaskDetailCountList->toArray();
    }

    /**
     * 查询本月制作总镜头数
     * @param $startTime
     * @param $endTime
     * @param $stepCategoryIdList
     * @param $planEpisodeIdList
     * @return int|mixed
     */
    public function getMakeShotTotal($startTime, $endTime, $stepCategoryIdList, $planEpisodeIdList)
    {
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $etqDb = (new TaskModel)->field("count(distinct task.entity_id) number")
            ->join('entity', "entity.id = task.entity_id join module on module.id = entity.module_id")
            ->where('task.step_type', 'step')
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedId);
        if (!empty($startTime) && !empty($endTime)) {
            $etqDb->whereBetween('task.plan_end_time', [$startTime, $endTime]);
        }
        if (!empty($planEpisodeIdList)) {
            $etqDb->whereIn('task.episode_id', $planEpisodeIdList);
        }
        if (!empty($stepCategoryIdList)) {
            $etqDb->whereIn('task.step_category_id', $stepCategoryIdList);
        }
        $makeEntityTotal = $etqDb->find();
        if (empty($makeEntityTotal['number'])) {
            return 0;
        }
        return $makeEntityTotal['number'];
    }

    /**
     * 全局异常统计
     * @param $param
     * @return array|null
     * @throws DataNotFoundException
     * @throws DbException
     * @throws HttpResponseException
     * @throws ModelNotFoundException
     */
    public function globalAbnormalCount($param): ?array
    {
        $queryCodeList = (array)$param['query_code_list'];
        if (empty($queryCodeList)) {
            return null;
        }
        $returnData = [];
        // 组内逾期任务数量统计
        if (in_array('workbench_overdue_task_group', $queryCodeList)) {
            $getGroupOverdueTaskCount = $this->getGroupOverdueTaskCount($param, \request()->getCurrentUserId(), \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_overdue_task_group',
                'count_data' => $getGroupOverdueTaskCount,
            ];
        }
        // 个人任务逾期任务数量统计
        if (in_array('workbench_overdue_task_personal', $queryCodeList)) {
            $getOverdueTaskCount = $this->getOverdueTaskPersonal($param, \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_overdue_task_personal',
                'count_data' => $getOverdueTaskCount,
            ];
        }
        // 逾期任务数(个人,组内合并)
        if (in_array('workbench_overdue_task', $queryCodeList)) {
            $getOverdueTaskCount = $this->getOverdueTaskCount($param, \request()->getCurrentUserId(), \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_overdue_task',
                'count_data' => $getOverdueTaskCount,
            ];
        }

        // history 任务逾期任务数量统计
        if (in_array('workbench_overdue_task_history', $queryCodeList)) {
            $getOverdueTaskHistory = TaskOverdueService::getInstance()->historyOverdueCount($param, \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_overdue_task_history',
                'count_data' => $getOverdueTaskHistory,
            ];
        }

        // 待处理工单
        if (in_array('workbench_pending_task_work_order', $queryCodeList)) {
            $getPendingTaskWorkOrderCount = $this->getPendingTaskWorkOrderCount($param, \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_pending_task_work_order',
                'count_data' => $getPendingTaskWorkOrderCount,
            ];
        }
        // 工单
        if (in_array('workbench_task_work_order_info', $queryCodeList)) {
            $getPendingTaskWorkOrderCount = $this->getWorkbenchTaskWorkOrder($param, \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_task_work_order_info',
                'count_data' => $getPendingTaskWorkOrderCount,
            ];
        }
        // 新增
        if (in_array('workbench_task_work_order', $queryCodeList)) {
            $getPendingTaskWorkOrderCount = $this->getTaskWorkOrderCount($param, \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_task_work_order',
                'count_data' => $getPendingTaskWorkOrderCount,
            ];
        }
        // 待处理工单-history
        if (in_array('workbench_pending_task_work_order_history', $queryCodeList)) {
            $getPendingTaskWorkOrderCount = WorkbenchHistoryService::getInstance()->getPendingTaskWorkOrderHistoryCount($param, \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_pending_task_work_order_history',
                'count_data' => $getPendingTaskWorkOrderCount,
            ];
        }
        // 返修任务
        if (in_array('workbench_repair_task', $queryCodeList)) {
            $getWorkbenchRepairTaskCount = $this->getWorkbenchRepairTaskCount($param, \request()->getSaasUserId(), \request()->getTenantId());
            $returnData[] = [
                'code' => 'workbench_repair_task',
                'count_data' => $getWorkbenchRepairTaskCount
            ];
        }
        // 待分配资产任务
        if (in_array('workbench_wait_assign_asset_task', $queryCodeList)) {
            $getWorkbenchWaitAssignTaskCount = $this->getWorkbenchWaitAssignTaskCount($param, $param['project_id'] ?? 0, 'asset');
            $returnData[] = [
                'code' => 'workbench_wait_assign_asset_task',
                'count_data' => $getWorkbenchWaitAssignTaskCount
            ];
        }
        // 待分配镜头任务
        if (in_array('workbench_wait_assign_shot_task', $queryCodeList)) {
            $getWorkbenchWaitAssignTaskCount = $this->getWorkbenchWaitAssignTaskCount($param, $param['project_id'] ?? 0, 'shot');
            $returnData[] = [
                'code' => 'workbench_wait_assign_shot_task',
                'count_data' => $getWorkbenchWaitAssignTaskCount
            ];
        }
        // 资产审核任务
        if (in_array('workbench_wait_review_asset_task', $queryCodeList)) {
            $getWorkbenchWaitReviewTaskCount = $this->getWorkbenchWaitReviewTaskCount($param, 'asset');
            $returnData[] = [
                'code' => 'workbench_wait_review_asset_task',
                'count_data' => $getWorkbenchWaitReviewTaskCount,
            ];
        }
        // 镜头审核任务
        if (in_array('workbench_wait_review_shot_task', $queryCodeList)) {
            $getWorkbenchWaitReviewTaskCount = $this->getWorkbenchWaitReviewTaskCount($param, 'shot');
            $returnData[] = [
                'code' => 'workbench_wait_review_shot_task',
                'count_data' => $getWorkbenchWaitReviewTaskCount,
            ];
        }
        // workbench_file_wait_update_task
        if (in_array('workbench_file_wait_update_task', $queryCodeList)) {
            $returnData[] = [
                'code' => 'workbench_file_wait_update_task',
                'count_data' => null,
            ];
        }
        return $returnData;
    }

    /**
     * 逾期任务数
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array
     */
    public function getOverdueTaskPersonal($param, $saasUserId, $tenantId): array
    {
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];
        $dbr = (new TaskModel)->field('count(*) as number,task.project_id as project_id')
            ->join('task_member', 'task_member.task_id = task.id', 'LEFT')
            ->where('task_member.user_id', $saasUserId)
            ->whereNotIn('task.step_type', TaskModel::STEP_TYPE_STEP_CATEGORY)
            ->where('task.end_time', 0)
            ->whereFieldRaw('task.plan_end_time', '<', time())
            ->whereRaw("get_json_string(`task`.`json`,'task_status') not in ($closedStatusId,$finalStatusId)");
        if (!empty($tenantId)) {
            $dbr->where('task.tenant_id', $tenantId);
        }
        if (!empty($param['start_time'])) {
            $dbr->whereFieldRaw('task.plan_end_time', '>=', $param['start_time']);
        }
        if (!empty($param['end_time'])) {
            $dbr->whereFieldRaw('task.plan_end_time', '<=', $param['end_time']);
        } else {
            $dbr->whereFieldRaw('task.plan_end_time', '>=', 1);
        }
        if (!empty($param['project_id']) && $param['project_id'] > 0) {
            $dbr->where('task.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $dbr->whereIn('task.project_id', $imUserProjectIds);
        }
        $list = $dbr->group('task.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 组内逾期任务数
     * @param $param
     * @param $userId
     * @param $saasUserId
     * @param $tenantId
     * @return array|null
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getGroupOverdueTaskCount($param, $userId, $saasUserId, $tenantId): ?array
    {
        // 获取当前用户所在部门
        $departmentIds = [];
        $departmentListQueryRes = DepartmentService::getInstance()->getUserDepartmentList($userId, $tenantId, ['headman', 'step_producer']);
        foreach ($departmentListQueryRes as $v) {
            $departmentIds[] = $v['department_id'];
        }
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];
        $dbr = TaskModel::field('count(*) as number,task.project_id as project_id')
            ->where('task.end_time', 0)
            ->whereRaw("task.step_type NOT IN ('step_category')")
            ->whereFieldRaw('task.plan_end_time', '<', time())
            ->whereRaw("get_json_string(`task`.`json`,'task_status') NOT IN ($closedStatusId,$finalStatusId)");
        if (!empty($tenantId)) {
            $dbr->where('task.tenant_id', $tenantId);
        }
        if (!empty($departmentIds)) {
            $dbr->whereIn('task.department_id', $departmentIds);
        }
        if (!empty($param['start_time'])) {
            $dbr->whereFieldRaw('task.plan_end_time', '>=', $param['start_time']);
        } else {
            $dbr->whereFieldRaw('task.plan_end_time', '>=', 1);
        }
        if (!empty($param['end_time'])) {
            $dbr->whereFieldRaw('task.plan_end_time', '<=', $param['end_time']);
        }
        if (!empty($param['project_id']) && $param['project_id'] > 0) {
            $dbr->where('task.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            $dbr->whereIn('task.project_id', $imUserProjectIds);
        }
        $list = $dbr->group('task.project_id')->select();
        if (empty($list->toArray())) {
            return null;
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 工作台迭代
     * 逾期任务数(个人,组内合并)
     * @param $param
     * @param $userId
     * @param $saasUserId
     * @param $tenantId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getOverdueTaskCount($param, $userId, $saasUserId, $tenantId): array
    {
        $defaultClosedTaskStatus = StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id');
        $dbr = (new TaskModel)
            ->field('count(*) as number,task.project_id as project_id')
            ->whereNotIn('task.step_type', TaskModel::STEP_TYPE_STEP_CATEGORY)
            ->where('task.end_time', 0)
            ->whereFieldRaw('task.plan_end_time', '>', 1)
            ->whereFieldRaw('task.plan_end_time', '<', time())
            ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", 'NOT IN', $defaultClosedTaskStatus);

        if (!empty($tenantId)) {
            $dbr->where('task.tenant_id', $tenantId);
        }

        if (!empty($param['project_id'])) {
            $dbr->where('task.project_id', $param['project_id']);
        }

        TaskStatisticsService::getInstance()->taskMyResponsibleFilter($dbr,
            query_array_construct([
                'department_id' => 'intval',
                'step_category_id' => 'split_array|array_map,intval',], $param),
            [$saasUserId], $tenantId
        );

        if (!($dbr instanceof Query)) {
            return [];
        }

        $list = $dbr->group('task.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }

        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 待处理工单
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]|null
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     * @throws HttpResponseException
     */
    public function getPendingTaskWorkOrderCount($param, $saasUserId, $tenantId): ?array
    {
        $db = (new \app\model\TaskWorkOrder)->field('task.project_id as project_id,count(distinct task_work_order.id) as number')
            ->join('task', 'task.id = task_work_order.task_id')
            ->join('task_work_order_user', 'task_work_order_user.work_order_id = task_work_order.id');

        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        if (!empty($closedId)) {
            $db->whereFieldRaw("get_json_string(task.`json`, 'task_status')", '<>', $closedId);
        }
        if (!empty($param['project_id'])) {
            $db->where('task.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return null;
            }
            $db->whereIn('task.project_id', $imUserProjectIds);
        }
        //环节
        if (!empty($param['step_category_id'])) {
            $db->whereIn('task_work_order.step_category_id', $param['step_category_id']);
        }
        // 我负责的传部门id
        $distinctUserId = [];
        if (!empty($param['department_id'])) {
            $departmentUserData = UserService::getInstance()->getDepartmentUserMultiDepartmentUser([$param['department_id']], $tenantId);
            $distinctUserId = $departmentUserData['distinct_user_ids'];
            if (!empty($distinctUserId)) {
                $saasUserId = $distinctUserId;
            }
            if (!empty($saasUserId)) {
                if (!is_array($saasUserId)) {
                    $saasUserId = [$saasUserId];
                }
                $db->whereIn('task_work_order_user.user_id', $saasUserId);
            }
        }


        // 计划完成时间筛选
        if (!empty($param['start_time'])) {
            $db->whereFieldRaw('task.plan_end_time', '>=', $param['start_time']);
        }
        if (!empty($param['end_time'])) {
            $db->whereFieldRaw('task.plan_end_time', '<=', $param['end_time']);
        }
        // 工单状态范围限制
        $untreatedString = OptionsService::getInstance()->getOptionsConfigItemData('task_work_order_status_config', 'untreated');
        if (empty($untreatedString)) {
            return [];
        }

        $untreatedStatus = explode(',', $untreatedString);
        $taskWorkOrderData = $db
            ->where('task_work_order.tenant_id', $tenantId)
            ->whereIn('task_work_order.status', $untreatedStatus)
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
//            ->where('task_work_order_user.user_id', $saasUserId)
            ->group('task.project_id')
            ->select();

        if (empty($taskWorkOrderData->toArray())) {
            return null;
        }
        $returnData = [];
        foreach ($taskWorkOrderData->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 工单情况统计
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWorkbenchTaskWorkOrder($param, $saasUserId, $tenantId): array
    {
        $db = (new TaskWorkOrder)
            ->field('task.project_id as project_id,count(distinct task_work_order.id) as number,' . "from_unixtime(task_work_order.created,'%Y-%m-%d') as date")
            ->join('task', 'task.id = task_work_order.task_id');

        //部门查询
        $departmentUserIds = [];
        if (!empty($param['department_id'])) {
            $departmentUserList = teamones_request('teamones-saas', 'tenant/select_department_user', ["param" => [
                "filter" => [
                    "department_id" => $param['department_id'],
                ]
            ]])['data']['rows'] ?? [];
            foreach ($departmentUserList as $userListItem) {
                $departmentUserIds[$userListItem['saas_user_id']] = $userListItem['saas_user_id'];
            }

            if (!empty($departmentUserIds)) {
                $param['department_work_order_user_type'] = $param['department_work_order_user_type'] ?? "receive";
                if ($param['department_work_order_user_type'] == "receive") {
                    $db->whereIn('task_work_order_user.user_id', $departmentUserIds);
                } else {
                    $db->whereIn('task_work_order.created_by', $departmentUserIds);
                }
            }
        }

        $isExistProblemStepCategory = false;
        $isExistTaskWorkOrderUser = false;
        $filter = [];
        foreach ($param as $key => $value) {
            if (strpos($key, "task_work_order_problem_step_category.") !== false) {
                $isExistProblemStepCategory = true;
                $filter[$key] = $value;
            }
            if (strpos($key, "task_work_order_user.") !== false) {
                $isExistTaskWorkOrderUser = true;
                $filter[$key] = $value;
            }

            if (strpos($key, "task_work_order.") !== false) {
                $filter[$key] = $value;
            }
        }

        if ($isExistProblemStepCategory) {
            $db->join('task_work_order_problem_step_category', 'task_work_order_problem_step_category.task_work_order_id = task_work_order.id');
        }

        if ($isExistTaskWorkOrderUser || !empty($departmentUserIds)) {
            $db->join('task_work_order_user', 'task_work_order_user.work_order_id = task_work_order.id');
        }

        if (!empty($param['project_id'])) {
            $db->where('task.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $db->whereIn('task.project_id', $imUserProjectIds);
        }

        // 创建时间筛选
        if (!empty($param['start_time'])) {
            $db->whereFieldRaw('task_work_order.created', '>=', $param['start_time']);
        }
        if (!empty($param['end_time'])) {
            $db->whereFieldRaw('task_work_order.created', '<=', $param['end_time']);
        }
        // 工单状态
        if (!empty($param['work_order_status'])) {
            $db->whereIn('task_work_order.status', $param['work_order_status']);
        }

        if (!empty($filter)) {
            $db->whereRaw(Enforcer::parseWhere($filter));
        }

        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $taskWorkOrderData = $db
            ->where('task_work_order.tenant_id', $tenantId)
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedStatusId)
            ->where('task_work_order.type', TaskWorkOrderModel::TYPE_TASK_MAKING)
            ->group('task.project_id,date')
            ->select()->toArray();
        if (empty($taskWorkOrderData)) {
            return [];
        }
        $returnData = [];
        foreach ($taskWorkOrderData as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
                'date' => $item['date']
            ];
        }
        return $returnData;
    }


    /**
     * 新增-总计产生工单数
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|null
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getTaskWorkOrderCount($param, $saasUserId, $tenantId): ?array
    {
        $db = (new TaskWorkOrder)->field('task.project_id as project_id,count(distinct task_work_order.id) as number')
            ->join('task', 'task.id = task_work_order.task_id')
            ->join('task_work_order_user', 'task_work_order_user.work_order_id = task_work_order.id');
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        if (!empty($closedId)) {
            $db->whereFieldRaw("get_json_string(task.`json`, 'task_status')", '<>', $closedId);
        }
        if (!empty($param['project_id'])) {
            $db->where('task.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return null;
            }
            $db->whereIn('task.project_id', $imUserProjectIds);
        }
        // 我负责的传部门id
        if (!empty($param['department_id'])) {
            $departmentUserData = UserService::getInstance()->getDepartmentUserMultiDepartmentUser([$param['department_id']], $tenantId);
            $distinctUserIds = $departmentUserData['distinct_user_ids'];
            $db->whereIn('task_work_order_user.user_id', $distinctUserIds);
        } else {
            $db->where('task_work_order_user.user_id', $saasUserId);
        }
        // 计划完成时间筛选
        if (!empty($param['start_time'])) {
            $db->whereFieldRaw('task.plan_end_time', '>=', $param['start_time']);
        }
        if (!empty($param['end_time'])) {
            $db->whereFieldRaw('task.plan_end_time', '<=', $param['end_time']);
        }
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $taskWorkOrderData = $db
            ->where('task_work_order.tenant_id', $tenantId)
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedStatusId)
            ->group('task.project_id')
            ->select();
        if (empty($taskWorkOrderData->toArray())) {
            return null;
        }
        $returnData = [];
        foreach ($taskWorkOrderData->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }


    /**
     * 返修任务数
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     */
    public function getWorkbenchRepairTaskCount($param, $saasUserId, $tenantId): array
    {
        $remakeStatusId = StatusService::getInstance()->byCode('remake')['id'];
        $dbr = (new TaskModel)->field('count(*) as number,task.project_id as project_id')
            ->where('executor', $saasUserId)
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->where('task.executor_tenant_id', $tenantId)
            ->whereFieldRaw("get_json_string(`task`.`json`, 'task_status')", $remakeStatusId);
        TaskStatisticsService::getInstance()->taskMyResponsibleFilter($dbr,
            query_array_construct([
                'department_id' => 'intval',
                'step_category_id' => 'split_array|array_map,intval',], $param),
            [$saasUserId], $tenantId
        );
        // 计划完成时间筛选
        return $this->taskPlanTimeProjectExtraData($param, $dbr, $saasUserId, $tenantId);
    }

    /**
     * 待分配任务
     * @param $param
     * @param $projectId
     * @param $moduleCode
     * @param array $taskStatus
     * @return array|array[]|null
     */
    public function getWorkbenchWaitAssignTaskCount($param, $projectId, $moduleCode): ?array
    {
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $dbr = TaskModel::field('count(*) as number,task.project_id as project_id')
            ->join('entity', 'entity.id = task.entity_id')
            ->join('module', 'entity.module_id = module.id')
            ->join('step', 'step.id = task.step_id')
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->where('task.tenant_id', \request()->getTenantId())
            ->where('task.assign_status', 'default')
            ->where('step.is_dismantle_step', 'no')
            ->whereRaw("get_json_string(task.`json`, '$.task_status') IN (" . StatusService::getInstance()->byCodeMap(['not_started', 'ready_to_start', 'wait_assemble'], false, 'id') . ")")
            ->whereRaw("get_json_string(entity.`json`, concat('$.', module.code, '_status')) <> {$closedStatusId}");
        if ($moduleCode == 'asset') {
            $dbr->whereRaw("task.entity_module_id in (select id from module where code in ('design','asset','level'))");
        }
        if ($moduleCode == 'shot') {
            $dbr->whereRaw("task.entity_module_id in (select id from module where code in ('shot'))");
        }
        if (!empty($projectId)) {
            $dbr->where('task.project_id', $projectId);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode(\request()->getSaasUserId(), \request()->getTenantId(), [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return null;
            }
            $dbr->whereIn('task.project_id', $imUserProjectIds);
        }
        TaskStatisticsService::getInstance()->taskMyResponsibleFilter($dbr,
            query_array_construct([
                'department_id' => 'intval',
                'step_category_id' => 'split_array|array_map,intval',], $param),
            [\request()->getSaasUserId()], \request()->getTenantId()
        );
        $list = $dbr->group('task.project_id')->select();
        if (empty($list->toArray())) {
            return null;
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 个人任务统计
     * @param $param
     * @return array|null
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function personalTaskCount($param): ?array
    {
        $queryCodeList = (array)$param['query_code_list'];
        if (empty($queryCodeList)) {
            return null;
        }

        $saasUserId = \request()->getSaasUserId();
        $tenantId = \request()->getTenantId();

        $returnData = [];
        // 待完成任务
        if (in_array('workbench_wait_done_task', $queryCodeList)) {
            $getWorkbenchWaitDoneTaskCount = $this->getWorkbenchWaitDoneTaskCount($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'workbench_wait_done_task',
                'count_data' => $getWorkbenchWaitDoneTaskCount,
            ];
        }
        // 我的资产审核任务
        if (in_array('workbench_wait_review_asset_task', $queryCodeList)) {
            $getWorkbenchWaitReviewTaskCount = $this->getWorkbenchWaitReviewTaskCount($param, 'asset');
            $returnData[] = [
                'code' => 'workbench_wait_review_asset_task',
                'count_data' => $getWorkbenchWaitReviewTaskCount,
            ];
        }
        // 我的镜头审核任务
        if (in_array('workbench_wait_review_shot_task', $queryCodeList)) {
            $getWorkbenchWaitReviewTaskCount = $this->getWorkbenchWaitReviewTaskCount($param, 'shot');
            $returnData[] = [
                'code' => 'workbench_wait_review_shot_task',
                'count_data' => $getWorkbenchWaitReviewTaskCount,
            ];
        }
        // 我的审核中任务
        if (in_array('workbench_my_reviewing_task', $queryCodeList)) {
            $getWorkbenchMyReviewingTaskCount = $this->getWorkbenchMyReviewingTaskCount($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'workbench_my_reviewing_task',
                'count_data' => $getWorkbenchMyReviewingTaskCount,
            ];
        }
        // 待签订框架合同
        if (in_array('workbench_wait_sign_framework_contract', $queryCodeList)) {
            $getWaitSignFrameworkContractCount = $this->getWaitSignFrameworkContractCount($tenantId);
            $returnData[] = [
                'code' => 'workbench_wait_sign_framework_contract',
                'count_data' => $getWaitSignFrameworkContractCount,
            ];
        }
        // 待接受订单
        if (in_array('workbench_wait_accept_order', $queryCodeList)) {
            $getWorkbenchWaitAcceptOrderCount = $this->getWorkbenchWaitAcceptOrderCount($tenantId);
            $returnData[] = [
                'code' => 'workbench_wait_accept_order',
                'count_data' => $getWorkbenchWaitAcceptOrderCount,
            ];
        }
        // 待排期内容
        if (in_array('workbench_wait_plan_content', $queryCodeList)) {
            $returnData[] = [
                'code' => 'workbench_wait_plan_content',
                'count_data' => [],
            ];
        }
        // 待交付任务
        if (in_array('workbench_wait_delivery_task', $queryCodeList)) {
            $getWorkbenchWaitDeliveryTaskCount = $this->getWorkbenchWaitDeliveryTaskCount($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'workbench_wait_delivery_task',
                'count_data' => $getWorkbenchWaitDeliveryTaskCount,
            ];
        }
        // 待我确认的排期
        if (in_array('workbench_wait_confirm_plan', $queryCodeList)) {
            $getWorkbenchWaitConfirmPlan = $this->getWorkbenchWaitConfirmPlan($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'workbench_wait_confirm_plan',
                'count_data' => $getWorkbenchWaitConfirmPlan,
            ];
        }
        // 待我审核的延期
        if (in_array('workbench_wait_review_task_delay', $queryCodeList)) {
            $getWorkbenchWaitReviewTaskDelay = $this->getWorkbenchWaitReviewTaskDelay($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'workbench_wait_review_task_delay',
                'count_data' => $getWorkbenchWaitReviewTaskDelay,
            ];
        }
        // 待上传文件
        if (in_array('wait_upload_file', $queryCodeList)) {
            $getWaitUploadFileTodoCount = $this->getWaitUploadFileTodoCount($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'wait_upload_file',
                'count_data' => $getWaitUploadFileTodoCount,
            ];
        }
        // 待下载
        if (in_array('wait_download_file', $queryCodeList)) {
            $getWaitDownloadFileCount = $this->getWaitDownloadFileCount($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'wait_download_file',
                'count_data' => $getWaitDownloadFileCount,
            ];
        }
        // 待审批的延期单
        if (in_array('wait_review_delay_order', $queryCodeList)) {
            $getWaitReviewDelayOrder = $this->getWaitReviewDelayOrder($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'wait_review_delay_order',
                'count_data' => $getWaitReviewDelayOrder,
            ];
        }
        // 未审批的延期单
        if (in_array('not_review_delay_order', $queryCodeList)) {
            $getNotReviewDelayOrder = $this->getNotReviewDelayOrder($param, $saasUserId, $tenantId);
            $returnData[] = [
                'code' => 'not_review_delay_order',
                'count_data' => $getNotReviewDelayOrder,
            ];
        }

        return $returnData;
    }

    /**
     * 待完成任务
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWorkbenchWaitDoneTaskCount($param, $saasUserId, $tenantId): array
    {
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $finalId = StatusService::getInstance()->byCode('final')['id'];
        $todoModuleId = ModuleService::getInstance()->byCode('todo')['id'];
        // 执行人是我 但是任务状态为未完成的任务
        $dbr = TaskModel::field('count(distinct task.id) as number,task.project_id as project_id')
            ->join('task_member', "task.id = task_member.task_id and task_member.user_id = {$saasUserId}")
            ->where('task.tenant_id', $tenantId)
            ->whereRaw("(task.step_type = 'step' and get_json_string(task.`json`, 'task_status') not in ($closedId,$finalId)) or (task.entity_module_id = {$todoModuleId} and task_member.status not in ($closedId,$finalId))")
            ->whereFieldRaw("get_json_string(task.`json`,'task_status')", '<>', $closedId);

        // 计划完成时间筛选
        return $this->taskPlanTimeProjectExtraData($param, $dbr, $saasUserId, $tenantId);
    }


    /**
     * 待审核任务
     * @param $param
     * @param $moduleCode
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWorkbenchWaitReviewTaskCount($param, $moduleCode): array
    {
        $tenantId = \request()->getTenantId();
        $saasUserId = \request()->getSaasUserId();
        $taskModuleId = ModuleService::getInstance()->byCode('task')['id'];
        $versionModuleId = ModuleService::getInstance()->byCode('version')['id'];
        // 审核人是我 并且当前到我审核
        $dbr = ReviewRequestModel::field("count(review_request.id) as number,review_request.project_id as project_id")
            ->join("version", "version.id = review_request.link_id and review_request.module_id = {$versionModuleId}")
            ->join("task", "task.id = version.link_id and version.module_id = {$taskModuleId}")
            ->where("review_request.module_id", $versionModuleId)
            ->where("review_request.tenant_id", $tenantId);
        if (empty($param['department_id']) && empty($param['step_category_id'])) {
            $dbr = $dbr->join("review_request_node", 'review_request_node.review_request_id = review_request.id')
                ->where("review_request_node.is_active", 'yes')
                ->where("review_request_node.plan_reviewer", $saasUserId);
        }
        if ($moduleCode == 'asset') {
            $dbr->whereRaw("task.entity_module_id in (select id from module where code in ('design','asset','level'))");
        }
        if ($moduleCode == 'shot') {
            $dbr->whereRaw("task.entity_module_id in (select id from module where code in ('shot'))");
        }
        if (!empty($param['project_id'])) {
            $dbr->where('review_request.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $dbr->whereIn('review_request.project_id', $imUserProjectIds);
        }
        TaskStatisticsService::getInstance()->taskMyResponsibleFilter($dbr,
            query_array_construct([
                'department_id' => 'intval',
                'step_category_id' => 'split_array|array_map,intval',], $param),
            [\request()->getSaasUserId()], \request()->getTenantId()
        );
        $list = $dbr->group('review_request.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 我的审核中的任务
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array[]
     */
    public function getWorkbenchMyReviewingTaskCount($param, $saasUserId, $tenantId): array
    {
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        // 执行人是我 但是任务状态为未完成的任务
        $dbr = TaskModel::field('count(*) as number,task.project_id as project_id')
            ->where('task.executor', $saasUserId)
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->where('task.tenant_id', $tenantId)
            ->whereRaw("get_json_string(task.`json`, 'task_status') in (select id from status where correspond = 'daily')")
            ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '<>', $closedId);

        return $this->taskPlanTimeProjectExtraData($param, $dbr, $saasUserId, $tenantId);
    }

    /**
     * 待签订框架合同
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWaitSignFrameworkContractCount($tenantId): array
    {
        $businessMode = TaskService::getInstance()->getBusinessMode();
        if (empty($businessMode)) {
            return ['count_data' => []];
        }
        $dbs = SupplierModel::field('count(distinct supplier.id) as number')
            ->join('tenant_authentication ata', 'ata.tenant_id = supplier.from_tenant_id')
            ->join('tenant_authentication bta', 'bta.tenant_id = supplier.tenant_id')
            ->where('ata.status', 'active')
            ->where('bta.status', 'active')
            ->where('ata.fdd_cert_status', '1')
            ->where('ata.fdd_auth_status', '4')
            ->where('bta.fdd_cert_status', '1')
            ->where('bta.fdd_auth_status', '4')
            ->where('supplier.type', 'outside')
            ->whereRaw("supplier.type = 'outside' and (supplier.from_tenant_id = {$tenantId} or supplier.tenant_id = {$tenantId})");

        if ($businessMode == 'both_sides_mode') {
            $dbs->where('supplier.framework_agreement_status', 'wait_for_sign');
        }
        if ($businessMode == 'tripartite_mode') {
            $dbs->where('tenant.framework_agreement_status', 'wait_for_sign');
        }
        $list = $dbs->select();
        if (empty($list->toArray())) {
            return ['count_data' => []];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id'] ?? 0),
            ];
        }
        return $returnData;
    }

    /**
     * 待接受订单
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWorkbenchWaitAcceptOrderCount($tenantId): array
    {
        $list = OrderModel::field("count(`order`.id) as number")
            ->where('order.status', 'wait_for_party_b_ensure')
            ->where('order.to_tenant_id', $tenantId)
            ->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id'] ?? 0),
            ];
        }
        return $returnData;
    }

    /**
     * 待交付任务
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWorkbenchWaitDeliveryTaskCount($param, $saasUserId, $tenantId): array
    {
        $finalId = StatusService::getInstance()->byCode('final')['id'];
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        // 排期计划完成而且执行人是我 但是任务状态为未完成的任务
        $dbr = TaskModel::field('count(*) as number,task.project_id as project_id')
            ->where('task.executor', $saasUserId)
            ->where('task.step_type', TaskModel::STEP_TYPE_STEP)
            ->where("task.tenant_id", $tenantId)
            ->whereFieldRaw("get_json_string(task.`json`, 'task_status')", '<>', $finalId)
            ->whereFieldRaw("get_json_string(`task`.`json`,'task_status')", '<>', $closedId);
        if (!empty($param['start_time']) && !empty($param['end_time'])) {
            $dbr->whereBetween('task.plan_delivery_time', [$param['start_time'], $param['end_time']]);
        }
        if (!empty($param['project_id'])) {
            $dbr->where('task.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $dbr->whereIn('task.project_id', $imUserProjectIds);
        }
        $list = $dbr->group('task.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }


    /**
     * 待我确认的排期
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWorkbenchWaitConfirmPlan($param, $saasUserId, $tenantId): array
    {
        $planModuleId = ModuleService::getInstance()->byCode('plan')['id'];
        // 审核人是我 并且当前到我审核
        $dbr = ReviewRequestModel::field("count(review_request.id) as number,review_request.project_id as project_id")
            ->join("review_request_node", "review_request_node.review_request_id = review_request.id")
            ->join('plan', "plan.id = review_request.link_id and review_request.module_id = {$planModuleId}")
            ->where('review_request.module_id', $planModuleId)
            ->where('review_request.tenant_id', $tenantId)
            ->where('review_request_node.is_active', 'yes')
            ->where('review_request_node.plan_reviewer', $saasUserId);
        if (!empty($param['end_time'])) {
            $dbr->whereFieldRaw('review_request_node.confirm_end_time', '<=', $param['end_time']);
        }
        if (!empty($param['project_id'])) {
            $dbr->where('review_request.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $dbr->whereIn('review_request.project_id', $imUserProjectIds);
        }
        $list = $dbr->group('review_request.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 待我审核的任务延期
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWorkbenchWaitReviewTaskDelay($param, $saasUserId, $tenantId): array
    {
        $taskModuleId = ModuleService::getInstance()->byCode('task')['id'];
        // 审核人是我 并且当前到我审核
        $dbr = ReviewRequestModel::field('count(review_request.id) as number,review_request.project_id as project_id')
            ->join('review_request_node', 'review_request_node.review_request_id = review_request.id')
            ->join('task', "task.id = review_request.link_id and review_request.module_id = {$taskModuleId}")
            ->where('review_request.module_id', $taskModuleId)
            ->where('review_request.tenant_id', $tenantId)
            ->where('review_request_node.is_active', 'yes');

        if (!empty($param['project_id'])) {
            $dbr->where('review_request.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $dbr->whereIn('review_request.project_id', $imUserProjectIds);
        }
        $list = $dbr->group('review_request.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }


    /**
     * 统计等待上传的文件记录
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWaitUploadFileTodoCount($param, $saasUserId, $tenantId): array
    {
        $sub = Db::table('order_task_upstream_version')
            ->alias('otuv')
            ->join('version v', ' otuv.version_id = v.id')
            ->join('task', 'task.id = v.link_id')
            ->field("max(v.id) as id")
            ->where('otuv.from_tenant_id', $tenantId)
            ->whereIn('task.executor_type', ['default', 'inside'])
            ->group('v.link_id');

        if (!empty($param['project_id'])) {
            $sub->where('v.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $sub->whereIn('v.project_id', $imUserProjectIds);
        }

        $subSql = $sub->buildSql();
        $list = Db::table("($subSql) as vm")->field("count(distinct v.id) as number,v.project_id as project_id")
            ->join('version v', 'v.id = vm.id')
            ->where('v.is_uploaded', 'no')
            ->group('v.project_id')
            ->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }


    /**
     * 统计等待下载的文件记录
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWaitDownloadFileCount($param, $saasUserId, $tenantId): array
    {
        $sub = Db::table("order_task_file_list")->alias('otfl')->field('max(otuv.id) as id')
            ->join("order_task_upstream_version otuv", 'otfl.task_id = otuv.task_id and otfl.to_tenant_id = otuv.to_tenant_id')
            ->join('version v', 'otuv.version_id = v.id')
            ->where('otfl.to_tenant_id', $tenantId)
            ->where('v.is_uploaded', 'yes');
        if (!empty($param['project_id'])) {
            $sub->where('otfl.to_project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $sub->whereIn('otfl.to_project_id', $imUserProjectIds);
        }
        $subSql = $sub->group('v.link_id')->buildSql();
        $list = Db::table("($subSql)")->alias('vm')->field("count(distinct dl.id) as number,otfl.to_project_id as project_id")
            ->join('order_task_upstream_version dl', 'dl.id = vm.id')
            ->join('order_task_file_list otfl', 'otfl.to_tenant_id = dl.to_tenant_id and otfl.task_id = dl.task_id')
            ->where('dl.is_download', 'no')
            ->group('otfl.to_project_id')
            ->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 待审批延期单-审批人是我,但是没有审批通过
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getWaitReviewDelayOrder($param, $saasUserId, $tenantId): array
    {
        $delayOrderTaskModuleId = ModuleService::getInstance()->byCode('delay_order_task')['id'];
        $dbr = ReviewRequestModel::field('count(distinct delay_order_task.delay_order_id) as number,review_request.project_id as project_id')
            ->join('delay_order_task', 'delay_order_task.id = review_request.link_id')
            ->join('review_request_node', 'review_request_node.review_request_id = review_request.id')
            ->where('review_request.module_id', $delayOrderTaskModuleId)
            ->where('review_request.tenant_id', $tenantId)
            ->where('review_request_node.is_active', 'yes')
            ->where('review_request.is_pass', 'no')
            ->where('review_request_node.plan_reviewer', $saasUserId);

        if (!empty($param['project_id'])) {
            $dbr->where('review_request.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $dbr->whereIn('review_request.project_id', $imUserProjectIds);
        }
        $list = $dbr->group('review_request.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }


    /**
     * 未审批延期单 - 当前用户创建的
     * @param $param
     * @param $saasUserId
     * @param $tenantId
     * @return array|array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function getNotReviewDelayOrder($param, $saasUserId, $tenantId): array
    {
        $delayOrderTaskModuleId = ModuleService::getInstance()->byCode('delay_order_task')['id'];
        $dbr = ReviewRequestModel::field('count(distinct delay_order_task.delay_order_id) as number,review_request.project_id as project_id')
            ->join('delay_order_task', 'delay_order_task.id = review_request.link_id')
            ->join('review_request_node', 'review_request_node.review_request_id = review_request.id')
            ->where('review_request.module_id', $delayOrderTaskModuleId)
            ->where('review_request.tenant_id', $tenantId)
            ->where('review_request_node.is_active', 'yes')
            ->where('review_request.is_pass', 'no')
            ->where('review_request.is_reviewed', 'no')
            ->where('review_request.created_by', $saasUserId);

        if (!empty($param['project_id'])) {
            $dbr->where('review_request.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $dbr->whereIn('review_request.project_id', $imUserProjectIds);
        }
        $list = $dbr->group('review_request.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }


    /**
     * @param $param
     * @param $dbr
     * @param $saasUserId
     * @param $tenantId
     * @return array
     */
    public function taskPlanTimeProjectExtraData($param, $dbr, $saasUserId, $tenantId): array
    {
        if (!empty($param['start_time'])) {
            $dbr->whereFieldRaw('task.plan_end_time', '>=', $param['start_time']);
        }
        if (!empty($param['end_time'])) {
            $dbr->whereFieldRaw('task.plan_end_time', '<=', $param['end_time']);
        }
        if (!empty($param['project_id'])) {
            $dbr->where('task.project_id', $param['project_id']);
        } else {
            $imUserProjectIds = ProjectService::getInstance()->getUserProjectIdListByRoleCode($saasUserId, $tenantId, [], ['in_progress']);
            if (empty($imUserProjectIds)) {
                return [];
            }
            $dbr->whereIn('task.project_id', $imUserProjectIds);
        }
        $list = $dbr->group('task.project_id')->select();
        if (empty($list->toArray())) {
            return [];
        }
        $returnData = [];
        foreach ($list->toArray() as $item) {
            $returnData[] = [
                'number' => intval($item['number']),
                'project_id' => intval($item['project_id']),
            ];
        }
        return $returnData;
    }

    /**
     * 部门交付情况 前置SQL拼接
     * @param Request $request
     * @param int $projectId
     * @param array $todoStepCategoryIdList
     * @param array $userIdList
     * @return Query
     */
    private function getGroupDeliveryCountBaseQuery(Request $request, int $projectId, array $todoStepCategoryIdList, array $userIdList): Query
    {
        $closedStatusId = StatusService::getInstance()->byCode('closed')['id'];
        $dbr = (new \app\model\TaskModel)->where('task.tenant_id', $request->getTenantId())
            ->whereFieldRaw('task.step_type', '<>', TaskModel::STEP_TYPE_STEP_CATEGORY)
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', $closedStatusId);

        if (!empty($projectId)) {
            $dbr = $dbr
                ->where('task.project_id', $projectId);
        }
        if (!empty($userIdList)) {
            $dbr = $dbr
                ->whereIn('task_member.partner', $userIdList)
                ->join('task_member', 'task_member.task_id = task.id');
        } else {
            $dbr = $dbr
                ->whereIn('task_member.partner', $request->getSaasUserId())
                ->join('task_member', 'task_member.task_id = task.id');
        }
        if (!empty($todoStepCategoryIdList)) {
            $stepDbr = TaskRelationTagModel::whereIn('link_module_code', ['step_category', 'show_step_category'])->buildSql();
            $dbr = $dbr
                ->whereIn('r_step_category.link_id', $todoStepCategoryIdList)
                ->join([$stepDbr => 'r_step_category'], 'r_step_category.task_id = task.id');
        }
        return $dbr;
    }

    /**
     * 部门交付情况
     * @param Request $request
     * @param int $projectId
     * @param int $departmentId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @return int[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function groupDeliveryCount(Request $request, int $projectId, int $departmentId, array $todoStepCategoryIdList, int $startTime, int $endTime): array
    {
        $res = [
            'delivery_total' => 0, // 计划交付
            'delivery_done' => 0, // 计划交付完成
            'delivery_actually' => 0, // 实际交付完成
        ];

        $userIdList = [];
        if (!empty($departmentId)) {
            $departmentService = DepartmentService::getInstance();
            $userIdList = $departmentService->getDepartmentUserIdList($departmentId);
            if (empty($userIdList)) {
                return $res;
            }
        }

        $finalStatusId = StatusService::getInstance()->byCode('final')['id'];
        $field = [
            'count(distinct if(task.plan_delivery_time between ' . $startTime . ' and ' . $endTime . ', task.id, null)) as total',
            'count(distinct if(task.plan_delivery_time between ' . $startTime . ' and ' . $endTime . ' and get_json_string(`task`.`json`, "$.task_status") =  ' . $finalStatusId . ', task.id, null)) as done',
            'count(distinct if(task.delivery_time between ' . $startTime . ' and ' . $endTime . ' and get_json_string(`task`.`json`, "$.task_status") =  ' . $finalStatusId . ', task.id, null)) as actually'
        ];
        $count = $this->getGroupDeliveryCountBaseQuery($request, $projectId, $todoStepCategoryIdList, $userIdList)
            ->whereIn('task.step_type', [
                TaskModel::STEP_TYPE_STEP,
                TaskModel::STEP_TYPE_STANDARD_MAKE_TO_DO,
                TaskModel::STEP_TYPE_TODO
            ])
            ->field($field)->select()->toArray();
        $count = array_shift($count);
        $res['delivery_total'] = intval($count['total']);
        $res['delivery_done'] = intval($count['done']);
        $res['delivery_actually'] = intval($count['actually']);
        return $res;
    }


    /**
     * 历史部门交付情况
     * @param Request $request
     * @param int $projectId
     * @param int $departmentId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @return array
     */
    public function groupDeliveryHistoryCount(Request $request, int $projectId, int $departmentId, array $todoStepCategoryIdList, int $startTime, int $endTime): array
    {
        return [];
        $data = $this->getGroupDeliveryCountBaseQuery($request, $projectId, $todoStepCategoryIdList, $userIdList)
            ->field($field)->select();
        if (empty($data->toArray())) {
            return [];
        }
        return array_map(function ($tmp) use ($endTime) {
            $tmp['delivery_total'] = intval($tmp['total']);
            $tmp['delivery_done'] = intval($tmp['done']);
            $tmp['delivery_actually'] = intval($tmp['actually']);
            $tmp['end_time'] = date('Y-m-d', $endTime);
            unset($tmp['total']);
            unset($tmp['done']);
            unset($tmp['actually']);
            return $tmp;
        }, $data->toArray());
    }

    /**
     * 部门异常任务情况
     * @param Request $request
     * @param int $projectId
     * @param int $departmentId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @param $today
     * @return int
     * @throws DbException
     */
    public function groupAbnormalAddCount(Request $request, int $projectId, int $departmentId, array $todoStepCategoryIdList, int $startTime, int $endTime, $today): int
    {
        $res = 0;
        // TODO get cached data by request_key
        $userIdList = [];
        if (!empty($departmentId)) {
            $departmentService = DepartmentService::getInstance();
            $userIdList = $departmentService->getDepartmentUserIdList($departmentId);
            if (empty($userIdList)) {
                return $res;
            }
        }
        if (empty($todoStepCategoryIdList) && empty($userIdList)) {
            return $res;
        }

        $rStepCategoryJoined = false;
        $addedTaskDb = TaskModel::join('task_member', 'task_member.task_id = task.id', 'left')
            ->where('task.tenant_id', $request->getTenantId())
            ->where('task.step_type', '<>', 'step_category')
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_is_append")', 'yes')
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', 3);

        if (!empty($startTime) && !empty($endTime)) {
            $addedTaskDb->whereBetween('task.created', [$startTime, $endTime]);
        }

        if (!empty($projectId)) {
            $addedTaskDb = $addedTaskDb
                ->where('task.project_id', $projectId);
        }
        if (!empty($todoStepCategoryIdList)) {
            if (!$rStepCategoryJoined) {
                $rStepCategoryJoined = true;
                $trtJoin = TaskRelationTagModel::whereIn('link_module_code', ["step_category", "show_step_category"])->buildSql();
                $addedTaskDb = $addedTaskDb
                    ->join([$trtJoin => 'r_step_category'], 'r_step_category.task_id = task.id');
            }
            $addedTaskDb = $addedTaskDb
                ->whereIn('r_step_category.link_id', $todoStepCategoryIdList);
        }
        if (!empty($userIdList)) {
            $addedTaskDb = $addedTaskDb
                ->whereIn('task_member.user_id', $userIdList);
        }
        return $addedTaskDb->count('distinct task.id');
    }

    /**
     * 剧集封板播出情况列表
     * @param Request $request
     * @param int $projectId
     * @param int $startTime
     * @param int $endTime
     * @return array[]
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function episodeSealShowList(Request $request, int $projectId, int $startTime, int $endTime): array
    {
        $res = [
            'plan_seal' => [],
            'plan_show' => [],
        ];

        $moduleService = ModuleService::getInstance();
        $planSeal = EntityModel::where([
            'project_id' => $projectId,
            'module_id' => $moduleService->byCode('episode')['id'],
        ])
            ->whereFieldRaw('get_json_string(`entity`.`json`, "$.episode_plan_seal_time")', 'BETWEEN', [$startTime, $endTime])
            ->field([
                'id as episode_id',
                'name as episode_name',
            ])
            ->fieldRaw('get_json_string(`entity`.`json`, "$.episode_plan_seal_time") as episode_plan_seal_time')
            ->fieldRaw('get_json_string(`entity`.`json`, "$.episode_plan_show_time") as episode_plan_show_time')
            ->select();
        $res['plan_seal'] = array_map(fn($item) => [
            'episode_id' => (int)$item['episode_id'],
            'episode_name' => $item['episode_name'],
            'episode_plan_seal_time' => (int)$item['episode_plan_seal_time'],
            'episode_plan_show_time' => (int)$item['episode_plan_show_time'],
        ], $planSeal->toArray());

        $planShow = EntityModel::where([
            'project_id' => $projectId,
            'module_id' => $moduleService->byCode('episode')['id'],
        ])
            ->whereFieldRaw('get_json_string(`entity`.`json`, "$.episode_plan_show_time")', 'BETWEEN', [$startTime, $endTime])
            ->field([
                'id as episode_id',
                'name as episode_name',
            ])
            ->fieldRaw('get_json_string(`entity`.`json`, "$.episode_plan_seal_time") as episode_plan_seal_time')
            ->fieldRaw('get_json_string(`entity`.`json`, "$.episode_plan_show_time") as episode_plan_show_time')
            ->select();
        $res['plan_show'] = array_map(fn($item) => [
            'episode_id' => (int)$item['episode_id'],
            'episode_name' => $item['episode_name'],
            'episode_plan_seal_time' => (int)$item['episode_plan_seal_time'],
            'episode_plan_show_time' => (int)$item['episode_plan_show_time'],
        ], $planShow->toArray());
        return $res;
    }


    /**
     * 未处理工单异常查询
     * @param Request $request
     * @param int $projectId
     * @param int $departmentId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @param $today
     * @return int
     * @throws DbException
     */
    public function groupAbnormalWorkOrderCount(Request $request, int $projectId, int $departmentId, array $todoStepCategoryIdList, int $startTime, int $endTime, $today): int
    {
        $res = 0;
        $taskMemberJoined = false;
        if (empty($todoStepCategoryIdList) && empty($departmentId)) {
            return $res;
        }
        $userIdList = [];
        $workOrderTaskDb = TaskModel::whereIn('task.step_type', ['work_order_to_do']);
        if (!empty($projectId)) {
            $workOrderTaskDb = $workOrderTaskDb
                ->where(['task.project_id' => $projectId]);
        }
        if (!empty($todoStepCategoryIdList)) {
            $trtJoin = TaskRelationTagModel::whereIn('link_module_code', ["step_category", "show_step_category"])->buildSql();
            $workOrderTaskDb = $workOrderTaskDb
                ->join([$trtJoin => 'r_step_category'], 'r_step_category.task_id = task.id')
                ->whereIn('r_step_category.link_id', $todoStepCategoryIdList);
        }
        if (!empty($userIdList)) {
            if (!$taskMemberJoined) {
                $workOrderTaskDb = $workOrderTaskDb
                    ->join('task_member', 'task_member.task_id = task.id');
                $taskMemberJoined = true;
            }
            $workOrderTaskDb = $workOrderTaskDb
                ->whereIn('task_member.partner', $userIdList);
        }
        if (!empty($departmentId)) {
            if (!$taskMemberJoined) {
                $workOrderTaskDb = $workOrderTaskDb
                    ->join('task_member', 'task_member.task_id = task.id');
                $taskMemberJoined = true;
            }
            $workOrderTaskDb = $workOrderTaskDb
                ->join('task_member_relation', 'task_member.id = task_member_relation.task_member_id')
                ->where('task_member_relation.department_id', $departmentId)
                ->where('task_member.partner', '>', 0);
        }

        return $workOrderTaskDb
            ->where('task.tenant_id', $request->getTenantId())
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', 3)
            ->whereRaw('get_json_string(`task`.`json`, "$.task_status") not in (3,7)')
            ->whereBetween('task.plan_end_time', [$startTime, $endTime])
            ->count('distinct task.id');
    }

    /**
     * 延期任务数
     * @param Request $request
     * @param int $projectId
     * @param int $departmentId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @param $today
     * @return int
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function groupAbnormalDelayTaskCount(Request $request, int $projectId, int $departmentId, array $todoStepCategoryIdList, int $startTime, int $endTime, $today): int
    {
        $res = 0;
        $userIdList = [];
        if (!empty($departmentId)) {
            $departmentService = DepartmentService::getInstance();
            $userIdList = $departmentService->getDepartmentUserIdList($departmentId);
            if (empty($userIdList)) {
                return $res;
            }
        }
        if (empty($userIdList) && empty($todoStepCategoryIdList)) {
            return $res;
        }
        //延期任务 review_request created,created_by ->user->tenant_user[department_id]
        $moduleService = ModuleService::getInstance();
        $taskModule = $moduleService->byCode('task');
        $rStepCategoryJoined = false;
        $reviewRequestDb = ReviewRequestModel::join('task', 'review_request.link_id = task.id')
            ->join('task_member', 'task_member.task_id = task.id', 'left')
            ->where([
                'review_request.module_id' => $taskModule['id'],
                'task.tenant_id' => $request->getTenantId(),
            ])
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', 3)
            ->whereBetween('review_request.created', [$startTime, $endTime]);
        if (!empty($projectId)) {
            $reviewRequestDb = $reviewRequestDb
                ->where('task.project_id', $projectId);
        }
        if (!empty($todoStepCategoryIdList)) {
            if (!$rStepCategoryJoined) {
                $rStepCategoryJoined = true;
                $trtJoin = TaskRelationTagModel::whereIn('link_module_code', ["step_category", "show_step_category"])->buildSql();
                $reviewRequestDb = $reviewRequestDb
                    ->join([$trtJoin => 'r_step_category'], 'r_step_category.task_id = review_request.link_id');
            }
            $reviewRequestDb = $reviewRequestDb
                ->whereIn('r_step_category.link_id', $todoStepCategoryIdList);
        }
        if (!empty($userIdList)) {
            $reviewRequestDb = $reviewRequestDb
                ->whereIn('task_member.user_id', $userIdList);
        }
        $res = $reviewRequestDb->count('distinct review_request.id');
        return $res;
    }

    /**
     * 上游交付情况BaseQuery
     * @param Request $request
     * @param int $projectId
     * @param array $upstreamStepCategoryIdList
     * @return Query
     */
    private function getGroupUpstreamDeliveryCountBaseQuery(Request $request, int $projectId, array $upstreamStepCategoryIdList): Query
    {
        $rscJoin = TaskRelationTagModel::whereIn('link_module_code', ["step_category", "show_step_category"])->buildSql();
//        $reJoin = TaskRelationTagModel::where('link_module_code', 'episode')->buildSql();
        $dbr = TaskModel::join([$rscJoin => 'r_step_category'], 'r_step_category.task_id = task.id')
//            ->join([$reJoin => 'r_episode'], 'r_episode.task_id = task.id')
            ->where('task.tenant_id', $request->getTenantId())
//            ->whereIn('task.step_type', ['step', 'standard_make_to_do'])
            ->where('task.step_type', 'step')
            ->whereIn('r_step_category.link_id', $upstreamStepCategoryIdList)
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', StatusService::getInstance()->byCode('closed')['id']);
        if (!empty($projectId)) {
            $dbr = $dbr->where('task.project_id', $projectId);
        }
        return $dbr
            ->group('r_step_category.link_id')
            ->field([
                'count(distinct task.id) as number',
                'r_step_category.link_id as id'
            ]);
    }

    /**
     * 上游标准任务交付情况BaseQuery
     * @param Request $request
     * @param $projectId
     * @param $todoStepCategoryIdList
     * @return mixed
     */
    private function getGroupUpstreamTodoDeliveryCountBaseQuery(Request $request, $projectId, $todoStepCategoryIdList)
    {
        ## 1.查询当前环节下所有的上游任务
        ## 2.匹配每个上游任务属于哪个环节
        ## 3.根据环节分组,返回数据
        $rscJoin = TaskRelationTagModel::whereIn('link_module_code', ["step_category", "show_step_category"])->buildSql();
        $dbr = TaskModel::join("task_relation", 'task_relation.target_task_id = task.id')
            ->join([$rscJoin => 'r_step_category'], 'r_step_category.task_id = task_relation.source_task_id') ## 查当前环节的上游任务
            ->join([$rscJoin => 'r_step_category_task'], 'r_step_category_task.task_id = task.id') ## 匹配当前环节上游任务所属哪个环节
            ->where('task.tenant_id', $request->getTenantId())
            ->where('task.project_id', $projectId)
            ->whereIn('r_step_category.link_id', $todoStepCategoryIdList)
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', '<>', StatusService::getInstance()->byCode('closed')['id']);
        return $dbr
            ->group('r_step_category_task.link_id')
            ->field([
                'count(distinct task.id) as number',
                'r_step_category_task.link_id as id',
                'array_join(array_distinct(array_agg(task.id)),",") as task_ids'
            ]);
    }

    /**
     * 上游交付情况
     * @param Request $request
     * @param array $param
     * @param int $projectId
     * @param int $departmentId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @param $today
     * @return array|mixed
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function groupUpstreamDeliveryCount(Request $request, array $param, int $projectId, int $departmentId, array $todoStepCategoryIdList, int $startTime, int $endTime, $today)
    {
        $res = [];

        $projectShowStepCategoryList = OptionsService::getInstance()->getProjectShowStepCategoryListConfig(0);

        $projectShowStepCategoryMap = [];
        foreach ($projectShowStepCategoryList as $datum) {
            $projectShowStepCategoryMap[$datum['id']] = empty($datum['show_name']) ? $datum['name'] : $datum['show_name'];
        }
        if (!empty($departmentId)) {
            $department = DepartmentService::getInstance()->getDepartment(['id' => $departmentId])->toArray();
            if (empty($department)) {
                return $res;
            }
            $department = array_shift($department);
            if (empty($department['step_category_ids'])) {
                return $res;
            }
            $todoStepCategoryIdList = array_unique(array_merge($todoStepCategoryIdList, param_to_array($department['step_category_ids'], ',', 'intval')));
        }
        if (empty($todoStepCategoryIdList)) {
            return $res;
        }
        $stepCategoryUpstreamDownstreamRelation = StepCategoryService::getInstance()->getStepCategoryUpstreamDownstreamRelation($todoStepCategoryIdList);
        $upstreamStepCategoryIdList = array_unique(array_merge(...array_column($stepCategoryUpstreamDownstreamRelation, 'upstream')));
        if (empty($upstreamStepCategoryIdList)) {
            return $res;
        }

        // 查询交付情况
        $upstreamDeliveryTotalCount = [];
        $upstreamDeliveryCount = [];
        $upstreamDeliveryActuallyCount = [];
        $upstreamDelayDeliveryCount = [];

        // 实时
        if ($param['history'] != 'yes') {
            // 计划交付 - 总数
            $qTotalRes = $this->getGroupUpstreamDeliveryCountBaseQuery($request, $projectId, $upstreamStepCategoryIdList)
                ->whereBetween('task.plan_delivery_time', [$startTime, $endTime])
                ->select()->toArray();
            $upstreamDeliveryTotalCount = array_column($qTotalRes, 'number', 'id');

            // 计划交付 - 已交付数
            $qDoneRes = $this->getGroupUpstreamDeliveryCountBaseQuery($request, $projectId, $upstreamStepCategoryIdList)
                ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', 7)
                ->whereBetween('task.plan_delivery_time', [$startTime, $endTime])
                ->select()->toArray();
            $upstreamDeliveryCount = array_column($qDoneRes, 'number', 'id');

            // 计划外交付
            $qOutDoneRes = $this->getGroupUpstreamDeliveryCountBaseQuery($request, $projectId, $upstreamStepCategoryIdList)
                ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', 7)
                ->whereNotBetween('task.plan_delivery_time', [$param['start_time'], $param['end_time']])
                ->whereBetween('task.delivery_time', [$startTime, $endTime])
                ->select()->toArray();
            $upstreamOutsideDeliveryCount = array_column($qOutDoneRes, 'number', 'id');

            // 实际交付 - 总数
            $qActuallyDoneRes = $this->getGroupUpstreamDeliveryCountBaseQuery($request, $projectId, $upstreamStepCategoryIdList)
                ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', 7)
                ->whereBetween('task.delivery_time', [$startTime, $endTime])
                ->select()->toArray();
            $upstreamDeliveryActuallyCount = array_column($qActuallyDoneRes, 'number', 'id');

            // 环节延期交付
            $dbr = DelayOrderModel::join('delay_order_task', 'delay_order_task.delay_order_id = delay_order.id')
                ->join('review_request', 'review_request.link_id = delay_order_task.id and review_request.module_id = ' . ModuleService::getInstance()->byCode('delay_order_task', 'id'))
                ->where([
                    'review_request.is_pass' => 'yes',
                    'review_request.is_reviewed' => 'yes',
                ])
                ->whereIn('delay_order.step_category_id', $upstreamStepCategoryIdList)
                ->whereBetween('review_request.reviewed', [$startTime, $endTime])
                ->field([
                    'count(distinct delay_order_task.task_id) as number',
                    'delay_order.step_category_id as id'
                ])
                ->group('delay_order.step_category_id');
            if (!empty($projectId)) {
                $dbr = $dbr->where('delay_order.project_id', $projectId);
            }
            $qDelayDeliveryRes = $dbr->select()->toArray();
            // 环节延期交付
            $upstreamDelayDeliveryCount = array_column($qDelayDeliveryRes, 'number', 'id');
        } else {
            // 计划交付 - 总数
//            $dotPassedJoin = TaskStatisticsService::getWindowedLastDelayOrderTask($projectId, $startTime, $endTime, 'yes', 'delivery');
            $dotPassedJoin = 'delay_order_task';
            $qTotalRes = $this->getGroupUpstreamDeliveryCountBaseQuery($request, $projectId, $upstreamStepCategoryIdList)
                ->join([$dotPassedJoin => 'dotr'], 'dotr.task_id = task.id and dotr.type in ("request_delay", "work_order") and dotr.manager_confirmed = "yes"', 'left')
                ->whereRaw('task.plan_delivery_time between ' . $startTime . ' and ' . $endTime
                    . ' or (dotr.id is not null and dotr.plan_delivery_time between ' . $startTime . ' and ' . $endTime . ')')
                ->select()->toArray();
            $upstreamDeliveryTotalCount = array_column($qTotalRes, 'number', 'id');

            // 计划交付 - 已交付数
            $qDoneRes = $this->getGroupUpstreamDeliveryCountBaseQuery($request, $projectId, $upstreamStepCategoryIdList)
                ->join([$dotPassedJoin => 'dotr'], 'dotr.task_id = task.id and dotr.type in ("request_delay", "work_order") and dotr.manager_confirmed = "yes"', 'left')
//                ->join([$dotPassedJoin => 'dotr'], 'dotr.task_id = task.id', 'left')
                ->whereRaw('task.plan_delivery_time between ' . $startTime . ' and ' . $endTime
                    . ' or (dotr.id is not null and dotr.plan_delivery_time between ' . $startTime . ' and ' . $endTime . ')')
                // 是否查询固化
//                ->where('task.delivery_time', '<=', $endTime)
                ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'))
                ->select()->toArray();
            $upstreamDeliveryCount = array_column($qDoneRes, 'number', 'id');

            // 计划外交付
            $qOutDoneRes = $this->getGroupUpstreamDeliveryCountBaseQuery($request, $projectId, $upstreamStepCategoryIdList)
                ->join([$dotPassedJoin => 'dotr'], 'dotr.task_id = task.id and dotr.type in ("request_delay", "work_order") and dotr.manager_confirmed = "yes"', 'left')
//                ->join([$dotPassedJoin => 'dotr'], 'dotr.task_id = task.id', 'left')
                ->whereRaw('task.plan_delivery_time not between ' . $startTime . ' and ' . $endTime
                    . ' and (dotr.id is null or dotr.plan_delivery_time not between ' . $startTime . ' and ' . $endTime . ')')
                ->whereBetween('task.delivery_time', [$param['start_time'], $param['end_time']])
                ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'))
                ->select()->toArray();
            $upstreamOutsideDeliveryCount = array_column($qOutDoneRes, 'number', 'id');

            // 实际交付 - 总数
            $qActuallyDoneRes = $this->getGroupUpstreamDeliveryCountBaseQuery($request, $projectId, $upstreamStepCategoryIdList)
                ->join('version', 'version.link_id = task.id', 'left')
                ->whereRaw('task.delivery_time between ' . $startTime . ' and ' . $endTime
                    . ' or version.published between ' . $startTime . ' and ' . $endTime)
                ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'))
                ->select()->toArray();
            $upstreamDeliveryActuallyCount = array_column($qActuallyDoneRes, 'number', 'id');
        }

        $resContainer = [
            'upstream_delivery_name_map' => [],
            'upstream_step_category_id_map' => [],
            'upstream_delivery_detail' => [],
        ];

        $upstreamDeliveryListIndex = 0;
        $stepCategoryUpstreamRelation = array_filter($stepCategoryUpstreamDownstreamRelation, fn($item) => in_array(intval($item['id']), $todoStepCategoryIdList));
        $upstreamStepCategoryIdList = array_unique(array_merge(...array_column($stepCategoryUpstreamRelation, 'upstream')));
        foreach ($upstreamStepCategoryIdList as $upstreamStepCategoryId) {

            $upstreamDeliveryTotal = intval($upstreamDeliveryTotalCount[$upstreamStepCategoryId] ?? 0);
            $upstreamDelivery = intval($upstreamDeliveryCount[$upstreamStepCategoryId] ?? 0);
            $upstreamDeliveryActually = intval($upstreamDeliveryActuallyCount[$upstreamStepCategoryId] ?? 0);
            $upstreamOutsideDelivery = intval($upstreamOutsideDeliveryCount[$upstreamStepCategoryId] ?? 0);
            $upstreamDelayDelivery = intval($upstreamDelayDeliveryCount[$upstreamStepCategoryId] ?? 0);
            if (empty($upstreamDeliveryTotal) && empty($upstreamDeliveryActually) && empty($upstreamDelayDelivery)) {
                continue;
            }
            $showName = $projectShowStepCategoryMap[$upstreamStepCategoryId];
            if (empty($showName)) {
                continue;
            }
            if (isset($resContainer['upstream_delivery_name_map'][$showName])) {
                $showNameIndex = $resContainer['upstream_delivery_name_map'][$showName];
                $resContainer['upstream_delivery_detail'][$showNameIndex]['step_category_id_list'][] = $upstreamStepCategoryId;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['step_category_id_list'] = array_unique($resContainer['upstream_delivery_detail'][$showNameIndex]['step_category_id_list']);
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_total'] += $upstreamDeliveryTotal;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_done'] += $upstreamDelivery;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_actually'] += $upstreamDeliveryActually;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_outside_plan'] += $upstreamOutsideDelivery;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delay_delivery_total'] += $upstreamDelayDelivery;
            } else {
                $resContainer['upstream_delivery_detail'][] = [
                    'step_category_id_list' => [$upstreamStepCategoryId],
                    'step_category_name' => $showName,
                    'delivery_total' => $upstreamDeliveryTotal,
                    'delivery_done' => $upstreamDelivery,
                    'delivery_actually' => $upstreamDeliveryActually,
                    'delivery_outside_plan' => $upstreamOutsideDelivery,
                    'delay_delivery_total' => $upstreamDelayDelivery,
                ];
                $resContainer['upstream_delivery_name_map'][$showName] = $upstreamDeliveryListIndex;
                $upstreamDeliveryListIndex++;
            }
        }
        return array_map(fn($item) => [
            'step_category_id_list' => $item['step_category_id_list'],
            'step_category_name' => $item['step_category_name'],
            'delivery_total' => $item['delivery_total'],
            'delivery_done' => $item['delivery_done'],
            'delivery_actually' => $item['delivery_actually'],
            'delivery_outside_plan' => $item['delivery_outside_plan'],
            'delay_delivery_total' => $item['delay_delivery_total'],
        ], $resContainer['upstream_delivery_detail'] ?? []);
    }


    /**
     * 上游标准任务交付情况
     * @param Request $request
     * @param array $param
     * @param int $projectId
     * @param int $departmentId
     * @param array $todoStepCategoryIdList
     * @param int $startTime
     * @param int $endTime
     * @param $today
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function groupUpstreamTodoDeliveryCount(Request $request, array $param, int $projectId, int $departmentId, array $todoStepCategoryIdList, int $startTime, int $endTime)
    {
        $res = [];
        $projectShowStepCategoryList = OptionsService::getInstance()->getProjectShowStepCategoryListConfig(0);
        $projectShowStepCategoryMap = [];
        foreach ($projectShowStepCategoryList as $datum) {
            $projectShowStepCategoryMap[$datum['id']] = empty($datum['show_name']) ? $datum['name'] : $datum['show_name'];
        }
        if (!empty($departmentId)) {
            $department = DepartmentService::getInstance()->getDepartment(['id' => $departmentId])->toArray();
            if (empty($department)) {
                return $res;
            }
            $department = array_shift($department);
            if (empty($department['step_category_ids'])) {
                return $res;
            }
            $todoStepCategoryIdList = array_unique(array_merge($todoStepCategoryIdList, param_to_array($department['step_category_ids'], ',', 'intval')));
        }
        if (empty($todoStepCategoryIdList)) {
            return $res;
        }

        $upstreamTodoRelationStepCategoryIdList = []; // 组织所有上游环节

        // 计划交付 - 总数
        $qTotalRes = $this->getGroupUpstreamTodoDeliveryCountBaseQuery($request, $projectId, $todoStepCategoryIdList)
            ->whereRaw('task.plan_delivery_time between ' . $startTime . ' and ' . $endTime)
            ->select()
            ->toArray();

        $upstreamDeliveryTotalCount = [];
        $upstreamDeliveryTotalIdsMap = [];
        if (!empty($qTotalRes)) {
            foreach ($qTotalRes as $qTotalItem) {
                $upstreamTodoRelationStepCategoryIdList[] = $qTotalItem['id'];
                $upstreamDeliveryTotalCount[$qTotalItem['id']] = $qTotalItem['number'];
                $upstreamDeliveryTotalIdsMap[$qTotalItem['id']] = $qTotalItem['task_ids'];
            }
        }

        // 计划交付 - 已交付数
        $qDoneRes = $this->getGroupUpstreamTodoDeliveryCountBaseQuery($request, $projectId, $todoStepCategoryIdList)
            ->whereRaw('task.plan_delivery_time between ' . $startTime . ' and ' . $endTime)
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'))
            ->select()
            ->toArray();

        $upstreamDeliveryCount = [];
        $upstreamDeliveryCountMap = [];
        if (!empty($qDoneRes)) {
            foreach ($qDoneRes as $qDoneItem) {
                $upstreamTodoRelationStepCategoryIdList[] = $qDoneItem['id'];
                $upstreamDeliveryCount[$qDoneItem['id']] = $qDoneItem['number'];
                $upstreamDeliveryCountMap[$qDoneItem['id']] = $qDoneItem['task_ids'];
            }

        }

        // 计划交付 - 未交付
        $qDeliveryRes = $this->getGroupUpstreamTodoDeliveryCountBaseQuery($request, $projectId, $todoStepCategoryIdList)
            ->whereRaw('task.plan_delivery_time between ' . $startTime . ' and ' . $endTime)
            ->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
            ->select()
            ->toArray();
        $inPlanNotDeliveryTotal = [];
        $inPlanNotDeliveryTotalMap = [];
        if (!empty($qDeliveryRes)) {
            foreach ($qDeliveryRes as $qDeliveryItem) {
                $upstreamTodoRelationStepCategoryIdList[] = $qDeliveryItem['id'];
                $inPlanNotDeliveryTotal[$qDeliveryItem['id']] = $qDeliveryItem['number'];
                $inPlanNotDeliveryTotalMap[$qDeliveryItem['id']] = $qDeliveryItem['task_ids'];
            }
        }

        // 计划外交付
        $qOutDoneRes = $this->getGroupUpstreamTodoDeliveryCountBaseQuery($request, $projectId, $todoStepCategoryIdList)
            ->whereRaw('task.plan_delivery_time not between ' . $startTime . ' and ' . $endTime)
            ->whereBetween('task.delivery_time', [$param['start_time'], $param['end_time']])
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'))
            ->select()
            ->toArray();
        $upstreamOutsideDeliveryCount = [];
        $upstreamOutsideDeliveryCountIds = [];
        if (!empty($qOutDoneRes)) {
            foreach ($qOutDoneRes as $qOutDoneItem) {
                $upstreamTodoRelationStepCategoryIdList[] = $qOutDoneItem['id'];
                $upstreamOutsideDeliveryCount[$qOutDoneItem['id']] = $qOutDoneItem['number'];
                $upstreamOutsideDeliveryCountIds[$qOutDoneItem['id']] = $qOutDoneItem['task_ids'];
            }
        }
        // 实际交付 - 总数
        $qActuallyDoneRes = $this->getGroupUpstreamTodoDeliveryCountBaseQuery($request, $projectId, $todoStepCategoryIdList)
            ->whereRaw('task.delivery_time between ' . $startTime . ' and ' . $endTime)
            ->whereFieldRaw('get_json_string(`task`.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'))
            ->select()
            ->toArray();
        $upstreamDeliveryActuallyCount = [];
        $upstreamDeliveryActuallyCountIds = [];
        if (!empty($qActuallyDoneRes)) {
            foreach ($qActuallyDoneRes as $qActuallyDoneItem) {
                $upstreamTodoRelationStepCategoryIdList[] = $qActuallyDoneItem['id'];
                $upstreamDeliveryActuallyCount[$qActuallyDoneItem['id']] = $qActuallyDoneItem['number'];
                $upstreamDeliveryActuallyCountIds[$qActuallyDoneItem['id']] = $qActuallyDoneItem['task_ids'];
            }
        }
        $resContainer = [
            'upstream_delivery_name_map' => [],
            'upstream_delivery_detail' => [],
        ];
        $upstreamDeliveryListIndex = 0;
        // 通过上游环节匹配分组
        foreach (array_unique($upstreamTodoRelationStepCategoryIdList) as $upstreamStepCategoryId) {
            if (in_array($upstreamStepCategoryId, $todoStepCategoryIdList)) {
                continue;
            }
            $upstreamDeliveryTotal = intval($upstreamDeliveryTotalCount[$upstreamStepCategoryId] ?? 0);
            $upstreamDeliveryTotalIds = $upstreamDeliveryTotalIdsMap[$upstreamStepCategoryId] ?? '';
            $upstreamDelivery = intval($upstreamDeliveryCount[$upstreamStepCategoryId] ?? 0);
            $upstreamDeliveryCountIds = $upstreamDeliveryCountMap[$upstreamStepCategoryId] ?? 0;
            $upstreamDeliveryActually = intval($upstreamDeliveryActuallyCount[$upstreamStepCategoryId] ?? 0);
            $upstreamDeliveryActuallyIds = $upstreamDeliveryActuallyCountIds[$upstreamStepCategoryId] ?? '';
            $upstreamOutsideDelivery = intval($upstreamOutsideDeliveryCount[$upstreamStepCategoryId] ?? 0);
            $upstreamOutsideDeliveryIds = $upstreamOutsideDeliveryCountIds[$upstreamStepCategoryId] ?? '';
            $inPlanNotDeliveryNum = intval($inPlanNotDeliveryTotal[$upstreamStepCategoryId] ?? 0);
            $inPlanNotDeliveryIds = $inPlanNotDeliveryTotalMap[$upstreamStepCategoryId] ?? '';
            if (empty($upstreamDeliveryTotal) && empty($upstreamDeliveryActually)) {
                continue;
            }
            $showName = $projectShowStepCategoryMap[$upstreamStepCategoryId];
            if (empty($showName)) {
                continue;
            }
            if (isset($resContainer['upstream_delivery_name_map'][$showName])) {
                $showNameIndex = $resContainer['upstream_delivery_name_map'][$showName];
                $resContainer['upstream_delivery_detail'][$showNameIndex]['step_category_id_list'][] = $upstreamStepCategoryId;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['step_category_id_list'] = array_unique($resContainer['upstream_delivery_detail'][$showNameIndex]['step_category_id_list']);
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_total'] += $upstreamDeliveryTotal;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_total_ids'] .= ',' . $upstreamDeliveryTotalIds;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_done'] += $upstreamDelivery;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_done_ids'] .= ',' . $upstreamDeliveryCountIds;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_actually'] += $upstreamDeliveryActually;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_actually_ids'] .= ',' . $upstreamDeliveryActuallyIds;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_outside_plan'] += $upstreamOutsideDelivery;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['delivery_outside_plan_ids'] .= ',' . $upstreamOutsideDeliveryIds;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['in_plan_not_delivery_total'] += $inPlanNotDeliveryNum;
                $resContainer['upstream_delivery_detail'][$showNameIndex]['in_plan_not_delivery_ids'] .= ',' . $inPlanNotDeliveryIds;
            } else {
                $resContainer['upstream_delivery_detail'][] = [
                    'step_category_id_list' => [$upstreamStepCategoryId],
                    'step_category_name' => $showName,
                    'delivery_total' => $upstreamDeliveryTotal,
                    'delivery_total_ids' => $upstreamDeliveryTotalIds,
                    'delivery_done' => $upstreamDelivery,
                    'delivery_done_ids' => $upstreamDeliveryCountIds,
                    'delivery_actually' => $upstreamDeliveryActually,
                    'delivery_actually_ids' => $upstreamDeliveryActuallyIds,
                    'delivery_outside_plan' => $upstreamOutsideDelivery,
                    'delivery_outside_plan_ids' => $upstreamOutsideDeliveryIds,
                    'in_plan_not_delivery_total' => $inPlanNotDeliveryNum,
                    'in_plan_not_delivery_ids' => $inPlanNotDeliveryIds,
                ];
                $resContainer['upstream_delivery_name_map'][$showName] = $upstreamDeliveryListIndex;
                $upstreamDeliveryListIndex++;
            }
        }

        return array_map(fn($item) => [
            'step_category_id_list' => $item['step_category_id_list'], // 环节
            'step_category_name' => $item['step_category_name'], // 环节名
            'delivery_total' => $item['delivery_total'], // 计划交付总数
            'delivery_total_ids' => $item['delivery_total_ids'], // 计划交付总数 task_ids
            'delivery_done' => $item['delivery_done'], // 计划交付-完成
            'delivery_done_ids' => $item['delivery_done_ids'],  // 计划交付-完成 task_ids
            'delivery_actually' => $item['delivery_actually'], // 实际交付
            'delivery_actually_ids' => $item['delivery_actually_ids'], // 实际交付ids
            'delivery_outside_plan' => $item['delivery_outside_plan'], // 计划外交付
            'delivery_outside_plan_ids' => $item['delivery_outside_plan_ids'], // 计划外交付 task_ids
            'in_plan_not_delivery_total' => $item['in_plan_not_delivery_total'], // 计划内未交付
            'in_plan_not_delivery_ids' => $item['in_plan_not_delivery_ids'], // 计划内未交付 task_ids
        ], $resContainer['upstream_delivery_detail'] ?? []);
    }

    /**
     * 异常任务统计 BaseQuery
     * @param Request $request
     * @param int $projectId
     * @param array $stepCategoryIdList
     * @param int $departmentId
     * @param int $startTime
     * @param int $endTime
     * @param string|null $type 查询类型: 全部/完成/交付异常
     * @return mixed
     */
    private function getTaskAbnormalCountBaseQuery(Request $request, int $projectId, array $stepCategoryIdList, int $departmentId, int $startTime = 0, int $endTime = 0, string $type = null)
    {
        $dotJoin = TaskStatisticsService::getWindowedLastDelayOrderTask($startTime, $endTime, ['original_plan_time', 'plan_delivery_time']);
        $dbr = TaskModel::join([$dotJoin => 'delay_task'], 'task.id = delay_task.task_id', 'left');
        $this->abnormalFilter($dbr, $stepCategoryIdList, $departmentId, $request->getTenantId());
        return $dbr->where('task.project_id', $projectId)
            ->whereNotIn('task.step_type', ['step_category', 'standard_make_to_do']);
    }


    /**
     * 异常任务统计
     * @param Request $request
     * @param array $param
     * @return int[]
     */
    public function taskAbnormalCount(Request $request, array $param): array
    {
        $res = [
            'total' => 0,
            'marked_today' => 0,
            'marked_delay' => 0,
            'in_progress' => 0,
            'in_wait' => 0,
        ];
        if (empty($param['start_time'])) {
            $timeRange = get_day_range_unix();
            $param['start_time'] = $timeRange['sdate'];
            $param['end_time'] = $timeRange['edate'];
        }
        if (empty($param['end_time'])) {
            $param['end_time'] = strtotime('+1 day', $param['start_time']);
        }

        if ($param['query_type'] == "overdue_strict") {
            $queryMap = [
                'total' => 'in_plan_overdue',
                'marked_today' => 'in_plan_marked_today',
                'marked_delay' => 'in_plan_marked_delay',
                'in_progress' => 'in_plan_in_progress',
                'in_wait' => 'in_plan_not_deal',
            ];
            foreach ($queryMap as $key => $code) {
                $queryParam = [
                    'project_id' => $param['project_id'],
                    'start_time' => $param['start_time'],
                    'end_time' => $param['end_time'],
                    'department_id' => $param['department_id'],
                    'step_category_id' => $param['todo_step_category_id_list'],
                ];
                $res[$key] = TaskStatisticsService::getInstance()->getTaskDynamicBaseQuery($request, $code, $queryParam)->count('distinct task.id');
            }
        } else {
            // 统计延期单
            $timeScope = ['original_plan_time', 'plan_delivery_time'];
            switch ($param['query_category']) {
                case "done":
                    $timeScope = ['original_plan_time'];
                    break;
                case "delivery":
                    $timeScope = ['plan_delivery_time'];
                    break;
            }
            $dotJoin = TaskStatisticsService::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], $timeScope);

            $noDealDbr = TaskModel::join([$dotJoin => 'dot'], 'dot.task_id = task.id', 'left');
            $this->abnormalFilter($noDealDbr, $param['todo_step_category_id_list'], $param['department_id'], $request->getTenantId(), $param['episode_id']);


            $doneTimeSearch = ["task.plan_end_time BETWEEN " . $param['start_time'] . " AND " . $param['end_time']];
            $deliveryTimeSearch = ["task.plan_delivery_time BETWEEN " . $param['start_time'] . " AND " . $param['end_time']];
            if ($param['history'] == 'yes') {
                $doneTimeSearch[] = "dot.manager_confirmed = 'yes' and dot.original_plan_time between " . $param['start_time'] . " and " . $param['end_time'];
                $deliveryTimeSearch[] = "dot.manager_confirmed = 'yes' and dot.plan_delivery_time between " . $param['start_time'] . " and " . $param['end_time'];
            }

            $notDoneNoDealSql = "get_json_string(task.`json`, '$.task_status') not in (" . StatusService::getInstance()->byCodeMap(['ready_to_publish', 'final', 'closed'], false, 'id') . ")"
                . " AND ((" . implode(" ) or ( ", $doneTimeSearch) . "))";
            $notDeliveryNoDealSql = "get_json_string(task.`json`, '$.task_status') not in (" . StatusService::getInstance()->byCodeMap(['final', 'closed'], false, 'id') . ")"
                . " AND ((" . implode(" ) or ( ", $deliveryTimeSearch) . "))";

            $whereNoDealSql = [];
            switch ($param['query_category']) {
                case "done":
                    $whereNoDealSql[] = $notDoneNoDealSql;
                    break;
                case "delivery":
                    $whereNoDealSql[] = $notDeliveryNoDealSql;
                    break;
                default:
                    $whereNoDealSql = [$notDoneNoDealSql, $notDeliveryNoDealSql];
            }
            $taskAbnormalCount = $noDealDbr->where('task.project_id', $param['project_id'])
                ->whereRaw('(' . implode(') or (', $whereNoDealSql) . ')')
                ->whereNotIn('task.step_type', ['step_category', 'standard_make_to_do'])
                ->whereIn('task.step_type', $param['task_step_type'])
                ->fieldRaw('count (distinct if(dot.task_id is null or (dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "no"), task.id, null)) as not_deal'
                    . ', count(distinct if(dot.task_id is not null and dot.type = "today_done", task.id, null)) as mark_today'
                    . ', count(distinct if(dot.task_id is not null and (dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "yes"), task.id, null)) as mark_delay'
                    . ', count(distinct if(dot.task_id is not null and (dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "default"), task.id, null)) as mark_delay_pending'
                )
                ->select()->toArray()[0];

            $res['in_wait'] = intval($taskAbnormalCount['not_deal'] ?? 0);
            $res['in_progress'] = intval($taskAbnormalCount['mark_delay_pending'] ?? 0);
            $res['marked_today'] = intval($taskAbnormalCount['mark_today'] ?? 0);
            $res['marked_delay'] = intval($taskAbnormalCount['mark_delay'] ?? 0);

            // total 满上， in_wait 未处理+处理中+处理不通过
            $res['total'] = $res['in_wait'] + $res['marked_delay'] + $res['marked_today'] + $res['in_progress'];
//            $res['in_wait'] += $res['in_progress'];
        }
        return $res;
    }

    /**
     * @param $db
     * @param array $stepCategoryId
     * @param int $departmentId
     * @param int $tenantId
     * @return void
     */
    public function abnormalFilter(&$db, array $stepCategoryId = [], int $departmentId = 0, int $tenantId = 0, $episode_id = []): void
    {
        //部门筛选
        if (!empty($departmentId) && !empty($tenantId)) {
            $db = $db->join('task_member', 'task.id = task_member.task_id')
                ->join('user', 'user.id = task_member.partner')
                ->join('im_user', 'user.union_id = im_user.id', 'left')
                ->join('tenant_user', 'user.union_id = tenant_user.user_id', 'left')
                ->join('tenant_user_department', 'tenant_user_department.tenant_user_id = tenant_user.id')
                ->where([
                    'tenant_user_department.department_id' => $departmentId,
                    'tenant_user.tenant_id' => $tenantId,
                    'im_user.active' => 1,
                ]);
        }

        // 环节筛选
        if (!empty($stepCategoryId)) {
            $trtJoin = TaskRelationTagModel::whereIn('link_module_code', ['step_category', 'show_step_category'])->buildSql();
            $db = $db->join([$trtJoin => 'task_relation_tag'], 'task_relation_tag.task_id = task.id')
                ->whereIn('task_relation_tag.link_id', $stepCategoryId);
        }

        // 剧集筛选
        if (!empty($episode_id)) {
            $trtJoin = TaskRelationTagModel::whereIn('link_module_code', ['episode'])->buildSql();
            $db = $db->join([$trtJoin => 'r_episode'], 'r_episode.task_id = task.id')
                ->whereIn('r_episode.link_id', $episode_id);
        }
    }

    /**
     * 环节审核中的剧集情况
     * @param $param
     * @return array|void
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function StepCategoryReviewingEpisodeList($param)
    {
        // 查询本月封板了的环节剧集
        $sealQ = (new \app\model\EpisodeStepCategorySealTimeModel)
            ->field("entity_id as episode_id")
            ->whereBetween('episode_step_category_seal_time.seal_time', [1, time()]);
        if (!empty($param['project_id'])) {
            $sealQ->where('episode_step_category_seal_time.project_id', $param['project_id']);
        }
        if (!empty($param['step_category_id_list'])) {
            $stepCategoryIdListWhere = '';
            foreach ((array)$param['step_category_id_list'] as $stepCategoryIdItem) {
                $stepCategoryIdListWhere .= " or (find_in_set({$stepCategoryIdItem}, episode_step_category_seal_time.step_category_ids) > 0)";
            }
            $stepCategoryIdListWhere = trim($stepCategoryIdListWhere, ' or');
            $sealQ->whereRaw($stepCategoryIdListWhere);
        }
        $sealEpisodeIdList = $sealQ->column('entity_id');
        if (empty($sealEpisodeIdList)) {
            return [];
        }

        $planField = [
            "entity.id as episode_id",
            "entity.code as episode_code",
            "min(plan.start_time) min_plan_start_time",
            "max(plan.end_time) max_plan_end_time",
            "min(task.name) as review_meeting_name",
            "max(get_json_string(task.`json`, '$.task_project_review_options_id')) as review_options_id"
        ];
        // 查询这些剧集里今天有审核排期的
        $closedId = StatusService::getInstance()->byCode('closed')['id'];
        $finalId = StatusService::getInstance()->byCode('final')['id'];
        $previewQ = (new \app\model\PlanModel)
            ->field($planField)
            ->join('task', 'task.id = plan.task_id')
            ->join('task_relation_tag r_step_category', "r_step_category.link_module_code in ('step_category','show_step_category') and r_step_category.task_id = plan.task_id")
            ->join('task_relation_tag r_episode', "r_episode.link_module_code in ('episode') and r_episode.task_id = plan.task_id")
            ->join('entity', 'entity.id = r_episode.link_id')
            ->where('plan.end_time', '<=', $param['end_time'])
            ->whereRaw("get_json_string(task.`json`,'$.task_status') NOT IN ($closedId,$finalId)")
            ->where('plan.type', 'review');
        if (!empty($param['project_id'])) {
            $previewQ->where('plan.project_id', $param['project_id']);
        }
        if (!empty($param['step_category_id_list'])) {
            $previewQ->whereIn('r_step_category.link_id', $param['step_category_id_list']);
        }
        $previewQ->whereRaw(sprintf("r_episode.link_id NOT IN (%s)", join(',', $sealEpisodeIdList)));

        // todo 选择了标签的 显示标签名称 没选的  直接展示待办名称
        $previewQList = $previewQ->group('entity.id,entity.code')->order('entity.code')->select();
        if (empty($previewQList->toArray())) {
            return [];
        }
        $sealEpisodeData = array_map(function ($tmp) {
            $tmp['episode_id'] = intval($tmp['episode_id']);
            $tmp['min_plan_start_time'] = intval($tmp['min_plan_start_time']);
            $tmp['max_plan_end_time'] = intval($tmp['max_plan_end_time']);
            $tmp['review_options_id'] = intval($tmp['review_options_id']);
            return $tmp;
        }, $previewQList->toArray());
        $res = $this->queryStepCategoryEpisodeDetail($param, $param['current_show_name'] ?? '', $sealEpisodeData, false);
        if (empty($res)) {
            return [];
        }
        return $res;
    }

}
